How to protect My Stored Procedure Code.

When deploying applications to a client's server(s) or to a shared SQL Server, there is often a concern that other people might peek at your business logic. Since often the code in a stored procedure can be proprietary, it is understandable that we might want to protect our T-SQL work. Use "WITH ENCRYPTION" option to protect your SQL code as shown below.

CREATE PROCEDURE dbo.Manvendra

WITH ENCRYPTION

AS

BEGIN

SELECT 'SQL statements'

END

 

Now when you will try to run sp_helptext to see the code, below error will appear.

"The text for object 'Manvendra' is encrypted"

How to find out how much CPU a SQL Server process is really using

Have you ever think about kpid in SQL Server when you look into sysprocesses system table.Its very useful coloumn when you are dealing with CPU pressure.When you look into the database server you see CPU utilization is very high and the SQL Server process is consuming most of the CPU. You launch SSMS and run sp_who2 and notice that there are a few SPIDs taking a long time to complete and these queries may be causing the high CPU pressure.
At the server level you can only see the overall SQL Server process, but within SQL Server you can see each individual query that is running. Is there a way to tell how much CPU each SQL Server process is consuming? In this article I explain how this can be done.
Find the below tip to get step by step process to identify a particular sql server process which is responsible for CPU pressure.

Tip: How to find out how much CPU a SQL Server process is really using

Enable Powershell Remoting on SQL Server Instances

Logging on to each SQL Server instance for daily DBA Administrative tasks can be quite lengthy. Are there any options available in PowerShell to reduce the effort and complexity of managing a SQL Server environment? Yes It is..Find this MSSQLTips for step by step process to enable Powershell Remoting and access SQL Server Instances remotly.

Tip:- Enable Powershell Remoting on SQL Server Instances

ASYNC_IO_COMPLETION Wait type

Normally this wait type can be seen in backup and restore transactions.and whenever you will see this wait type your backup/restore process will be in suspended state most of the time because the process is waiting to get IO resource to proceed its operation and it will wait till certain time period then moved in suspended state. In that case your process will take more than its usual time to complete or most of the time it will hung or will showing in executing state for unknown time duration.

This wait type occurs when a task is waiting for asynchronous I/Os to finish. This wait type is normally seen with few other wait types like BACKUPBUFFER,BUCKIO etc. This is clear indication of DISK IO issue.You can also get the Average disk queue length or current disk queue length value at the same time when you are getting this wait type.Compare both counters and if these counters have high value then you should look into your storage subsystem. Identify disk bottlenecks, by using Perfmon Counters, Profiler, sys.dm_io_virtual_file_stats and SHOWPLAN.

Any of the following reduces these waits:

1.      Adding additional IO bandwidth.

2.      Balancing IO across other drives.

3.      Reducing IO with appropriate indexing.

4.     Check for bad query plans.

5.     Check for memory pressure

 We can also corelate this wait type between Memory pressure and Disk IO subsystem issues.

BACKUPBUFFER Wait Type

Today I was working on an issue in which backup job was showing in executing state since last 2 days.Initially my observation was either it was hung in middle of its process or it was processing very slow.Here i decided to see the lock wait type for this process.when i gather this info i see the wait type was BACKUPBUFFER wait type.

BACKUPBUFFER Wait Type:-This wait type Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount. This wait stats will occur when you are taking the backup on the tape or any other extremely slow backup system.This wait type can be seen with one more wait type i.e. ASYNC_IO_COMPLETION. 

These both wait type indicates that the issue is with storage/disk subsystem.So solution is to either ask your storage team to look in this or change your disk/storage subsystem and run your backup on a healthy and fast storage subsystem.

Apple iPod touch 32 GB (4th Generation) NEWEST MODEL