SQL Server Wait Statistics Query

 

WITH [Waits] AS

    (SELECT

        [wait_type],

        [wait_time_ms] / 1000.0 AS [WaitS],

        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],

        [signal_wait_time_ms] / 1000.0 AS [SignalS],

        [waiting_tasks_count] AS [WaitCount],

        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],

        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]

    FROM sys.dm_os_wait_stats

    WHERE [wait_type] NOT IN (

        -- These wait types are almost 100% never a problem and so they are

        -- filtered out to avoid them skewing the results. Click on the URL

        -- for more information.

        N'BROKER_EVENTHANDLER', 

        N'BROKER_RECEIVE_WAITFOR', 

        N'BROKER_TASK_STOP', 

        N'BROKER_TO_FLUSH', 

        N'BROKER_TRANSMITTER', 

        N'CHECKPOINT_QUEUE', 

        N'CHKPT', 

        N'CLR_AUTO_EVENT', 

        N'CLR_MANUAL_EVENT', 

        N'CLR_SEMAPHORE', 

        -- Maybe comment this out if you have parallelism issues

        N'CXCONSUMER', 

        -- Maybe comment these four out if you have mirroring issues

        N'DBMIRROR_DBM_EVENT', 

        N'DBMIRROR_EVENTS_QUEUE', 

        N'DBMIRROR_WORKER_QUEUE', 

        N'DBMIRRORING_CMD', 

        N'DIRTY_PAGE_POLL', 

        N'DISPATCHER_QUEUE_SEMAPHORE', 

        N'EXECSYNC', 

        N'FSAGENT', 

        N'FT_IFTS_SCHEDULER_IDLE_WAIT', 

        N'FT_IFTSHC_MUTEX', 

 

       -- Maybe comment these six out if you have AG issues

        N'HADR_CLUSAPI_CALL', 

        N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 

        N'HADR_LOGCAPTURE_WAIT', 

        N'HADR_NOTIFICATION_DEQUEUE', 

        N'HADR_TIMER_TASK', 

        N'HADR_WORK_QUEUE', 

        N'KSOURCE_WAKEUP', 

        N'LAZYWRITER_SLEEP', 

        N'LOGMGR_QUEUE', 

        N'MEMORY_ALLOCATION_EXT', 

        N'ONDEMAND_TASK_QUEUE', 

        N'PARALLEL_REDO_DRAIN_WORKER', 

        N'PARALLEL_REDO_LOG_CACHE', 

        N'PARALLEL_REDO_TRAN_LIST', 

        N'PARALLEL_REDO_WORKER_SYNC', 

        N'PARALLEL_REDO_WORKER_WAIT_WORK', 

        N'PREEMPTIVE_OS_FLUSHFILEBUFFERS', 

        N'PREEMPTIVE_XE_GETTARGETSTATE', 

        N'PWAIT_ALL_COMPONENTS_INITIALIZED', 

        N'PWAIT_DIRECTLOGCONSUMER_GETNEXT', 

        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', 

        N'QDS_ASYNC_QUEUE', 

        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',

        N'QDS_SHUTDOWN_QUEUE', 

        N'REDO_THREAD_PENDING_WORK', 

        N'REQUEST_FOR_DEADLOCK_SEARCH', 

        N'RESOURCE_QUEUE', 

        N'SERVER_IDLE_CHECK', 

        N'SLEEP_BPOOL_FLUSH', 

        N'SLEEP_DBSTARTUP', 

        N'SLEEP_DCOMSTARTUP', 

        N'SLEEP_MASTERDBREADY', 

        N'SLEEP_MASTERMDREADY', 

        N'SLEEP_MASTERUPGRADED', 

        N'SLEEP_MSDBSTARTUP', 

        N'SLEEP_SYSTEMTASK', 

        N'SLEEP_TASK', 

        N'SLEEP_TEMPDBSTARTUP', 

        N'SNI_HTTP_ACCEPT', 

        N'SOS_WORK_DISPATCHER', 

        N'SP_SERVER_DIAGNOSTICS_SLEEP', 

        N'SQLTRACE_BUFFER_FLUSH', 

        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 

        N'SQLTRACE_WAIT_ENTRIES', 

        N'VDI_CLIENT_OTHER', 

        N'WAIT_FOR_RESULTS', 

        N'WAITFOR', 

        N'WAITFOR_TASKSHUTDOWN', 

        N'WAIT_XTP_RECOVERY', 

        N'WAIT_XTP_HOST_WAIT', 

        N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', 

        N'WAIT_XTP_CKPT_CLOSE', 

        N'XE_DISPATCHER_JOIN', 

        N'XE_DISPATCHER_WAIT', 

        N'XE_TIMER_EVENT' 

        )

    AND [waiting_tasks_count] > 0

    )

SELECT

    MAX ([W1].[wait_type]) AS [WaitType],

    CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],

    CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],

    CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],

    MAX ([W1].[WaitCount]) AS [WaitCount],

    CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],

    CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],

    CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],

    CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S]

FROM [Waits] AS [W1]

INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]

GROUP BY [W1].[RowNum]

HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 95; -- percentage threshold

GO

Find SQL Services running on a Instance

dm_server_services is a useful system view which provides detail about SQL Services running on a instance and other good feature is that it shows even the cluster node on which SQL Server services are running.

Query
SELECT servicename, 
  startup_type_desc, 
  status_desc, 
  last_startup_time, 
  service_account, 
  is_clustered, cluster_nodename
FROM sys.dm_server_services

Output 
servicename,startup_type_desc,status_desc,last_startup_time,service_account,is_clustered,cluster_nodename
SQL Server (MSSQLSERVER),Automatic,Running,2013-05-31 23:31:38.7849183 -07:00,domain\user,N,NULL
SQL Server Agent (MSSQLSERVER),Automatic,Running,2013-05-31 23:31:46.0190158 -07:00,domain\user,N,NULL

Find Number of Days in a Given Month

How many lines of code do you need to write to get number of days in a given month , there is no handy function available in SQL Server which does the job.

Say 12 If statements or combine Month which has 30 and 31 with just IF Statements .. And how about February which may have 28 or 29 depending on leap year or not.

Below mentioned code returns data with single statement when a date is provided as input.

SET NOCOUNT ON

DECLARE @dt DATETIME
SELECT  @dt = '2012-07-01'
SELECT DAY(DATEADD(MONTH,1,DATEADD(DAY,1-DAY(@dt),@dt))-1) AS DaysInGivenMonth


/*
--Section below explains logic step of step how this is calculated...

SELECT DAY(@dt) 'Day from the date specified'
SELECT 1-DAY(@dt) 'One day before the date'
SELECT DATEADD(DAY,1-DAY(@dt),@dt) 'Find the start of month'
SELECT DATEADD(MONTH,1,DATEADD(DAY,1-DAY(@dt),@dt)) 'Find prev month'
SELECT DATEADD(MONTH,1,DATEADD(DAY,1-DAY(@dt),@dt))-1 'Subtract one day'

SET NOCOUNT OFF
*/

In SQL 2012

DECLARE @dt DATETIME
SELECT  @dt = '2012-07-01'
SELECT DATEPAT(dd,EOMONTH (@dt))

What is Collation and is it that Important ?

Collation is defined as set of rules which determines how data comparison and data sorting would take place. Character data is sorted using correct character sequence and with options for specifying case and accent sensitivity.

Example:
For case sensitive rule, character A and a are different as it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97.

For accent sensitive rule, character "ä” and "a" are different as it uses ASCII code to differentiate the input. The ASCII value of a is 97 and ä is 225.

Levels:
First Level , specified at instance level during SQL Installation.

Second level , when we create a database, one can specify the default collation used for the database. If you don’t specify a collation, database is created with the default collation of the instance.

Third level, when we define a character column, you can specify its collation. If you do not specify a collation, the column is created with the default collation of the database.

We cannot specify collation for character variables and are always created with the default collation of the database.

General Recommendation:
System Databases collation cannot be changed directly and would require rebuild or reinstall of SQL Server system databases which would need downtime.

Database or column Collation could be changed at any point of time without downtime , but best practice is to keep user database in same collation as System Databases.

Name (required)

Email Address (required, will not be published)

Website Address

<%= commentBody %>

Generate HTML reports from SQL Server

Do you know generating HTML reports via SQL Server is so simple using Web Assistant Procedures , please have a look at below steps to try out the same.

First Step to enable Web Assistant via sp_configure to use that feature.
sp_configure 'Web Assistant Procedures' ,1
reconfigure with override

Second step, this could be done via SQL Server or go to C Drive and create a folder named report for SQL Server to save the generated HTML report.

sp_configure 'xp_cmdshell' ,1
reconfigure with override

xp_cmdshell 'md C:\report'

This is the magic procedure which generates HTML report. And you could send absolutely any query and output is formatted in a neat manner in HTML page.

For example below we are sending Drive Free Space information i.e. procedure and next is getting list of databases and their status , each of it gets created as seperate tables in report.

Exec sp_makewebtask @outputfile    =    'C:\report\DB_REPORT.HTM',
                                     @query           =   
 'exec master..xp_fixeddrives
 select name,DATABASEPROPERTYEX(name,''Status'') AS Status from sysdatabases'

P.S:- Please note this feature is there in SQL 2005 and not in SQL 2000 nor in SQL 2008.

Find Last Index Rebuild Time for all Databases on SQL Server

IF EXISTS(SELECT 'X' FROM tempdb..sysobjects where name like '#TABLE_VER%')
drop table #TABLE_VER
CREATE TABLE #TABLE_VER (DBNAME VARCHAR(50), INDEX_DATE DATETIME)

DECLARE @command varchar(1000)

SELECT @command =
'USE [?] INSERT INTO #TABLE_VER SELECT db_name() ,
(SELECT max(STATS_DATE(object_id, index_id)) FROM sys.indexes)'

EXEC sp_MSforeachdb @command

SELECT ' Index Rebuild Date'
UNION
SELECT '**************************************************************'
union
SELECT 'Index Rebuild Ran on '+ DBNAME + ' ON ' + CONVERT(VARCHAR(12),INDEX_DATE) FROM #TABLE_VER WHERE DBNAME NOT IN ('master','tempdb','model','msdb')

Audit Server Configurations

This script will output configuration informations as below:

• Server Version Configurations like SQL Server Edition , Service Pack , Authentication , Clustered/Non Clustered etc.
• Database File Details like Data & Log File Path of System Databases , TempDB , User Databases and the overal size of each databases.
• Memory and sp_configure enabled configurations and also lists the Logical and Physical CPU count.
• Trace flags & Other startup Parameters , TCP Ports , Named Pipes Protocol Information
• Backup details like Last Full backup , last differential backup and last transaction log backup with size , backep up to Tape or Shared Location or Local Drive with path of backup share.

***********************************************************************************

SET NOCOUNT ON

DECLARE @TABLE_VER TABLE (ProductVersion VARCHAR(50),ProductLevel VARCHAR(50),
Edition VARCHAR(100),DotNetVersion VARCHAR(100),Collation VARCHAR(100),
Instance VARCHAR(100),MachineName VARCHAR(100),ServerName VARCHAR(100),
Clustering VARCHAR(50),Authentication VARCHAR(100))

INSERT INTO @TABLE_VER
SELECT
CONVERT(VARCHAR,SERVERPROPERTY('ProductVersion')) AS ProductVersion,
CONVERT(VARCHAR,SERVERPROPERTY('ProductLevel')) AS ProductLevel,
CONVERT(VARCHAR,SERVERPROPERTY('Edition')) AS Edition,
--CASE SERVERPROPERTY('EngineEdition')
--WHEN 1 THEN 'Desktop/Personal'
--WHEN 2 THEN 'Standard'
--WHEN 3 THEN 'Enterprise'
--WHEN 4 THEN 'Express'
--END  EngineEdition,
CONVERT(VARCHAR,SERVERPROPERTY('BuildClrVersion')) AS DotNetVersion,
CONVERT(VARCHAR,SERVERPROPERTY('Collation')) AS Collation,
CONVERT(VARCHAR,SERVERPROPERTY('InstanceName')) as Instance,
CONVERT(VARCHAR,SERVERPROPERTY('MachineName')) AS MachineName,
CONVERT(VARCHAR,SERVERPROPERTY('ServerName')) AS ServerName,
CASE SERVERPROPERTY('IsClustered')
WHEN 1 THEN 'Clustered' ELSE 'Non-Clustered' END AS 'Clustering',
CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 1 THEN 'Windows Authentication'
ELSE 'Both Windows & SQL' END AS 'Authentication'

SELECT ' Server Version Details'
UNION
SELECT '**************************************************************'
UNION
SELECT '1>ServerName is: ' + ServerName FROM @TABLE_VER
UNION
SELECT '2>MachineName is: ' + MachineName FROM @TABLE_VER
UNION
SELECT '3>Instance is: ' + ISNULL(Instance,'Default') FROM @TABLE_VER
UNION
SELECT '4>ProductVersion is: ' + ProductVersion FROM @TABLE_VER
UNION
SELECT '5>ProductLevel is: ' + ProductLevel FROM @TABLE_VER
UNION
SELECT '6>Edition is: ' + Edition FROM @TABLE_VER
UNION
SELECT '7>DotNetVersion is: ' + DotNetVersion FROM @TABLE_VER
UNION
SELECT '8>Server Collation is: ' + Collation FROM @TABLE_VER
UNION
SELECT '9>Authentication is: ' + Authentication FROM @TABLE_VER
UNION
SELECT '91>Clustering is: ' + Clustering FROM @TABLE_VER

SELECT ' File Details'
UNION
SELECT '**************************************************************'
UNION
SELECT '1>System Databases Data Path is: ' + SUBSTRING(filename,1,LEN(filename) - CHARINDEX('\',REVERSE(filename)))
FROM master..sysaltfiles where DB_NAME(dbid) = 'master' AND fileid = 1
UNION
SELECT '2>System Databases Log Path is: ' + SUBSTRING(filename,1,LEN(filename) - CHARINDEX('\',REVERSE(filename)))
FROM master..sysaltfiles where DB_NAME(dbid) = 'master' AND fileid = 2
UNION
SELECT '3>TempDB Databases Data Path is: ' + SUBSTRING(filename,1,LEN(filename) - CHARINDEX('\',REVERSE(filename)))
FROM master..sysaltfiles where DB_NAME(dbid) = 'tempdb' AND fileid <> 2
UNION
SELECT '4>TempDB Databases Log Path is: ' + SUBSTRING(filename,1,LEN(filename) - CHARINDEX('\',REVERSE(filename)))
FROM master..sysaltfiles where DB_NAME(dbid) = 'tempdb' AND fileid = 2
UNION
SELECT '5>User Databases Data Path is: ' + SUBSTRING(filename,1,LEN(filename) - CHARINDEX('\',REVERSE(filename)))
FROM master..sysaltfiles where DB_NAME(dbid) not in ('master','msdb','model','tempdb') AND fileid <> 2
UNION
SELECT '6>User Databases Log Path is: ' + SUBSTRING(filename,1,LEN(filename) - CHARINDEX('\',REVERSE(filename)))
FROM master..sysaltfiles where DB_NAME(dbid) not in ('master','msdb','model','tempdb') AND fileid = 2

SELECT ' Database Details'
UNION
select 'Database:' + DB_NAME(dbid) +  'Size(in MB) is : ' + CONVERT(VARCHAR,SUM(size/128)) + ' MB' from master..sysaltfiles group by dbid
SELECT '**************************************************************'
UNION
select ' Config & Memory Details'
UNION
SELECT 'Config: ' + convert(varchar,name) + ' | Value: ' + convert(varchar,value_in_use)
from sys.configurations where value_in_use <> 0
and configuration_id not in ( 115,116,117,505,1126,1127,1519,1520,1531,1536,1538,1540,1541,1543,1544,1557,
1563,1565,1567,1568,1573,1575,16387)
UNION
select 'Minimum Memory (in MB): ' + convert(varchar,value_in_use )
from sys.configurations where name in ('min server memory (MB)')
union
select 'Maximum Memory (in MB): ' + convert(varchar,value_in_use )
from sys.configurations where name in ('max server memory (MB)')
union
SELECT 'Logical CPU Count: ' + convert(varchar,cpu_count) + ', Physical CPU Count: '
+ convert(varchar,cpu_count / hyperthread_ratio)
FROM sys.dm_os_sys_info
DECLARE @table_err TABLE (LogDate datetime,Processinfo VARCHAR(50),Params VARCHAR(7000))
DECLARE @I INTEGER
SELECT @I = 0
WHILE @I < 10
BEGIN
 INSERT INTO  @table_err
 EXEC master..xp_readerrorlog @I,1,'Server','Startup'
 INSERT INTO  @table_err
 EXEC master..xp_readerrorlog @I,1,'Server','Server local connection'
 INSERT INTO  @table_err
 EXEC master..xp_readerrorlog @I,1,'Server','listening'

 IF (SELECT COUNT('X') FROM @table_err) > 0
 SELECT @I = 100

 SELECT @I = @I + 1
END

SELECT ' Startup Parameters & Protocol Details'
UNION
SELECT '**************************************************************'
UNION
SELECT Params FROM @table_err

SELECT ' Backup Details'
UNION
SELECT '**************************************************************'
UNION
select 'Last Full Backup of ' + CONVERT(varchar,database_name) + ' happened on ' +
  convert(varchar,backup_finish_date) + '.Size of Backup(in MB) is ' +
  convert(varchar,cast(backup_size/1048576 as DECIMAL(10, 2))) + '.Database Recovery is ' +
  convert(varchar,recovery_model) + char(13) +
  'Backed up to ' + case device_type when 2 then 'Disk' when 5 then 'Tape' when 7 then 'Virtual Device' end
  + '.Backup Path is ' + physical_device_name
from msdb..backupset o , msdb..backupmediafamily f
where backup_finish_date = ( select max(backup_finish_date) from msdb..backupset i
                                          where i.database_name = o.database_name and type = 'D')
and type = 'D' and database_name in ( select name from master..sysdatabases)
and o.media_set_id = f.media_set_id
UNION
select 'Last Differential Backup of ' + CONVERT(varchar,database_name) + ' happened on ' +
  convert(varchar,backup_finish_date) + '.Size of Backup(in MB) is ' +
  convert(varchar,cast(backup_size/1073741824 as DECIMAL(10, 2)))
from msdb..backupset o
where backup_finish_date = ( select max(backup_finish_date) from msdb..backupset i
                                          where i.database_name = o.database_name and type = 'I')
and type = 'I' and database_name in ( select name from master..sysdatabases)
UNION
select 'Last Log Backup of ' + CONVERT(varchar,database_name) + ' happened on ' +
  convert(varchar,backup_finish_date) + '.Size of Backup(in MB) is ' +
  convert(varchar,cast(backup_size/1073741824 as DECIMAL(10, 2)))
from msdb..backupset o
where backup_finish_date = ( select max(backup_finish_date) from msdb..backupset i
                                          where i.database_name = o.database_name and type = 'L')
and type = 'L' and database_name in ( select name from master..sysdatabases)

COUNT(*) Versus IF EXISTS.

This style of coding is often followed by many developers where in we check whether records are present in table by using SELECT * or COUNT(*).But the question is are we using the
right method to instead for just checking existence of records and the answer is no.

For a developer it is nice to see records in table for verification but it would be still very nicer if one has followed the highly efficient EXISTS clause from a performance stand point, which performs many a times better compared to COUNT(*) as the execution chain breaks up as soon it locates a value but in COUNT(*) it scans the entire table to find the count of records which would prove to be costly and not satisfying our need

As always let us see an example to understand this better:

SET NOCOUNT ON
SET STATISTICS IO OFF

IF EXISTS ( SELECT 'X' FROM sysobjects where TYPE = 'U' and name = 'TEST_COUNT_EXISTS')
DROP TABLE TEST_COUNT_EXISTS
GO

CREATE TABLE TEST_COUNT_EXISTS
( DOC_ID INT IDENTITY(1,1) NOT NULL,
DOC_NAME VARCHAR(255) NOT NULL,
LANG_ID INT NOT NULL,
DOC_PATH VARCHAR(2000) NOT NULL
)

CREATE INDEX IX_LANG_ID ON TEST_COUNT_EXISTS(LANG_ID)

DECLARE @iCount INT
SELECT @iCount = 1

--Populate Table with 100000 rows
WHILE @iCount < = 100000
BEGIN
INSERT INTO TEST_COUNT_EXISTS(DOC_NAME,LANG_ID,DOC_PATH)
SELECT 'DOC_NAME' + CONVERT(VARCHAR,@iCount), 1 , '\\MYCOMP\PATH\' + CONVERT(VARCHAR,@iCount)
SELECT @iCount = @iCount + 1
END

--Make the sample random by using GUID
UPDATE TEST_COUNT_EXISTS
SET LANG_ID = 1
WHERE DOC_ID IN ( SELECT TOP 10000 DOC_ID FROM TEST_COUNT_EXISTS ORDER BY NEWID())

UPDATE TEST_COUNT_EXISTS
SET LANG_ID = 2
WHERE DOC_ID IN ( SELECT TOP 10000 DOC_ID FROM TEST_COUNT_EXISTS ORDER BY NEWID())

UPDATE TEST_COUNT_EXISTS
SET LANG_ID = 3
WHERE DOC_ID IN ( SELECT TOP 10000 DOC_ID FROM TEST_COUNT_EXISTS ORDER BY NEWID())


SET STATISTICS IO ON

IF (SELECT COUNT(*) FROM TEST_COUNT_EXISTS WHERE LANG_ID = 3) > 0
SELECT 'COUNT(*)'

IF EXISTS (SELECT * FROM TEST_COUNT_EXISTS WHERE LANG_ID = 3)
SELECT 'EXISTS'

/* Output
Table 'TEST_COUNT_EXISTS'. Scan count 1, logical reads 141, physical reads 0, read-ahead reads 31, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TEST_COUNT_EXISTS'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

/* Output

By the above you we can infer that its EXISTS performs better when compared to a COUNT(*).

SQL Monitoring Tool – SQL Profiler

SQL Profiler is a wonderful tool provided along with SQL Server Setup to monitor activities running on a SQL Server at any point of time. However people might wonder I get the same when I run sp_who or sp_who2 and what extra one gets by using profiler. Any other monitoring tool would provide process or connection level information and we have to use multiple steps to locate what activity is performed by a particular process and that is where SQL Profiler is handy.

Now let us know see how to use SQL Profiler and when to SQL Profiler

Start Menu-> Run and type profiler, in the UI provide server/User and password and then choose default trace and click on run and we could commands which are fired against the server.

The basic information we receive from profiler are TextData, Application Name, LoginName, Reads, Writes, CPU, Duration, SPId, StartTime, EndTime of which main parameter is TextData which shows the data sent from the client to the server along with the command executed.

Say for example I execute say a procedure Proc_test with Parameter1 value as "Test" and Parameter2 value as 120 then the TextData would show value as

EXEC Proc_Test "Test",120

This is very useful for troubleshooting as many of times we end up with application users saying its a query or procedure bug and we find finally that application has passed a hard coded value as the application developer forgot to change the code or parameter values were jumbled. So first thing any database developer needs to do when a bug or issue is logged as saying no rows returned or wrong data fetched from procedure would be to run profiler and confirm correct values are passed to database before analyzing the issue which would save lots of human errors and speedy recovery of issues.

There are other useful information which are available which help us troubleshoot from performance perspective
Reads - How much of reads is performed for each statements which would help us determine how much data is read from disk instead of buffer
Writes - How much of Writes is performed for each statement from which we can again know how much of IO is being consumed
CPU - CPU is the amount of CPU time the query consumed
Duration - Duration is the amount of time it took to execute the statement.

Needless to say that duration is the first parameter to see and locate statements which has more duration and then take the statement from Textdata and analyze further on whether reads and writes for those statements are appropriate or tuning is required from Index perspective or re-write query logic. To get statement level we need to use SQLBatchCompleted as RPCCompleted would just return procedure level and not statement level.

And also as a responsible developer we need to understand that SQL Profiler also consumes some amount of CPU on the server as it monitors the server continuously and
so it is restricted to only sysadmins.

Also we need to use SQL Profiler cautiously and not choose this for all troubleshooting issues , so talk to the DBA when in real need and DBAs can give permission to user for
running the same.

One more good option we get from profiler would be save these traces as a trace file or to SQL Server table so we can analyze these traces in future for troubleshooting.

In the coming weeks we would see some more features on the SQL Profiler to leverage this tool in a still better way to solve many of our issues…………

Dynamic Statements - sp_executesql Vs EXEC

Dynamic Statements are ineffective to use as SQL Server would not be able to determine a execution during compilation time and so SQL Server would create the execution plan
each and every time when such statements are executed.

The greatest advantage of Stored Procedure apart from reusability is that the code is compiled and so SQL would save some execution time by not invoking compiler for creating
creating execution plan. What would happen if we need to use dynamic statements in our procedure due to a business need would the same be compiled once and then cached or does it each time create execution plan ?

Answer would be it depends on how we invoke dynamic statements. Generally there are 2 ways of invoking dynamic statements one to EXEC statement and the next one would be
to use sp_executesql . Of which sp_executesql is the one which needs to be used as the plan is cached for the same.

As always let us see an example to understand this better:

IF EXISTS ( SELECT 'X' FROM sysobjects where TYPE = 'U' and name = 'TEST_SYS_CACHE')
DROP TABLE TEST_SYS_CACHE
GO


CREATE TABLE TEST_SYS_CACHE
( Col1_Int INT IDENTITY(1,1) NOT NULL,
Col2_Varchar VARCHAR(255) NOT NULL,
Col3_Varchar VARCHAR(2000) NOT NULL,
COl4_Flag BIT NOT NULL
)

--Poplulate 1000 rows
DECLARE @iCount INTEGER
SELECT @iCount = 1

WHILE @iCount < = 1000
BEGIN

INSERT INTO TEST_SYS_CACHE(Col2_Varchar,Col3_Varchar,COl4_Flag)
SELECT 'ABC' + CONVERT(VARCHAR,@iCount),'\\ABC' + CONVERT(VARCHAR,@iCount) + '\Share',1

SELECT @iCount = @iCount + 1

END

DBCC FREEPROCCACHE

--Execute this query multiple times and you would see usecount is always 1
DECLARE @Col_Value_E INT
DECLARE @Qry_Cache_E VARCHAR(7000)
SELECT @Col_Value_E = 100
SELECT @Qry_Cache_E = 'SELECT * FROM TEST_SYS_CACHE WHERE Col1_Int = ' + CONVERT(VARCHAR,@Col_Value_E)
EXEC (@Qry_Cache_E)
SELECT sql,usecounts,* from sys.syscacheobjects WHERE objtype = 'Prepared'

DBCC FREEPROCCACHE

--Execute this query multiple times and you would see usecount keeps increasing
DECLARE @Col_Value INT
DECLARE @Qry_Cache NVARCHAR(4000)
SELECT @Col_Value = 100
SELECT @Qry_Cache = 'SELECT * FROM TEST_SYS_CACHE WHERE Col1_Int = @Col_Value'
EXEC sp_executesql @Qry_Cache, N'@Col_Value INT', @Col_Value = @Col_Value
SELECT sql,usecounts,* from sys.syscacheobjects WHERE objtype = 'Prepared'


/* Output
sql usecounts
(@1 tinyint)SELECT * FROM [TEST_SYS_CACHE] WHERE [Col1_Int]=@1 1


sql usecounts
(@Col_Value INT)SELECT * FROM TEST_SYS_CACHE WHERE Col1_Int = @Col_Value 4

/* Output

So from the above we can infer that sp_executesql plan is cached and used for subsequent runs but for EXEC each time the plan is recreated