If your box is CPU bound then this is the script you need. The first query will order the results based on the queries that have used the most CPU time since the SQL Server instance has been restarted (or the server has been rebooted). The second query orders the results based upon the average CPU time that each query takes. [cc lang=”sql”] — Find queries that take the most CPU overall SELECT TOP 50 ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid) ,TextData = qt.text ,DiskReads = qs.total_physical_reads — The worst reads, disk reads ,MemoryReads = qs.total_logical_reads –Logical Reads are memory reads ,Executions = qs.execution_count ,TotalCPUTime = qs.total_worker_time ,AverageCPUTime = qs.total_worker_time/qs.execution_count ,DiskWaitAndCPUTime = qs.total_elapsed_time ,MemoryWrites = qs.max_logical_writes ,DateCached = qs.creation_time ,DatabaseName = DB_Name(qt.dbid) ,LastExecutionTime = qs.last_execution_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY qs.total_worker_time DESC — Find queries that have the highest average CPU usage SELECT TOP 50 ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid) ,TextData = qt.text ,DiskReads = qs.total_physical_reads — The worst reads, disk reads ,MemoryReads = qs.total_logical_reads –Logical Reads are memory reads ,Executions = qs.execution_count ,TotalCPUTime = qs.total_worker_time ,AverageCPUTime = qs.total_worker_time/qs.execution_count ,DiskWaitAndCPUTime = qs.total_elapsed_time ,MemoryWrites = qs.max_logical_writes ,DateCached = qs.creation_time ,DatabaseName = DB_Name(qt.dbid) ,LastExecutionTime = qs.last_execution_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY qs.total_worker_time/qs.execution_count DESC [/cc]
Continue reading ...
This query returns back the queries that use the most IO. This can mean that either the query is reading from disk more than usual or occupying and utilizing a large amount of buffer cache. These are typical symptoms of queries that do not have the proper indexes or queries that simply read a lot of data. [cc lang=”sql”] /********************************************************** * top procedures memory consumption per execution * (this will show mostly reports & jobs) ***********************************************************/ SELECT TOP 100 * FROM ( SELECT DatabaseName = DB_NAME(qt.dbid) ,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid) ,DiskReads = SUM(qs.total_physical_reads) — The worst reads, disk reads ,MemoryReads = SUM(qs.total_logical_reads) –Logical Reads are memory reads ,Executions = SUM(qs.execution_count) ,IO_Per_Execution = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count) ,CPUTime = SUM(qs.total_worker_time) ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time) ,MemoryWrites = SUM(qs.max_logical_writes) ,DateLastExecuted = MAX(qs.last_execution_time) FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt GROUP BY DB_NAME(qt.dbid), OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid) ) T ORDER BY IO_Per_Execution DESC /********************************************************** * top procedures memory consumption total * (this will show more operational procedures) ***********************************************************/ SELECT TOP 100 * FROM ( SELECT DatabaseName = DB_NAME(qt.dbid) ,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid) ,DiskReads = SUM(qs.total_physical_reads) — The worst reads, disk reads ,MemoryReads = SUM(qs.total_logical_reads) –Logical Reads are memory reads ,Total_IO_Reads = SUM(qs.total_physical_reads + qs.total_logical_reads) ,Executions = SUM(qs.execution_count) ,IO_Per_Execution = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count) ,CPUTime = SUM(qs.total_worker_time) ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time) ,MemoryWrites = SUM(qs.max_logical_writes) ,DateLastExecuted = MAX(qs.last_execution_time) FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt GROUP BY DB_NAME(qt.dbid), OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + […]
SQL Server 2008 has now introduced minimally logged inserts into tables that already contain data and a clustered index. What happens is the initial inserts may be fully logged if the data pages they are filling already contain data. However any new data pages added to the table will be minimally logged if all the requirements below are met. Trace flag 610 must be on Database recovery model must be bulk-logged or Simple Inserted data must be ordered by the clustered index To turn on the trace flag for your current session: [cc lang=”sql”] DBCC TRACEON (610) INSERT INTO dbo.MyTable SELECT * FROM ORDER BY 1 DBCC TRACEOFF (610) [/cc] This new change differs dramatically from the previous requirements for minimal logging. Previously there could be no clustered index and a table lock had to be acquired on the target table. For more information, visit: Minimal Logging Changes – MSDN Blog
Continue reading ...
Ever wonder why sometimes data transfer can be lightning fast while other times you’re watching sp_who2 wondering when it’s going to finish? It’s likely you’re noticing the difference between minimal logging and full logging. Even in a simple recovery model for a database you can experience row inserts to both the transaction log and the data pages. The easiest way to take advantage of minimal logging is to set the database recovery model to simple, drop all indexes in the target table then use SSIS, DTS, or BULK INSERT to transfer the data in. The speed of inserting data in SQL Server is wholly dependent on how many writes occur to the transaction log. These writes occur in two different modes, Minimal logging and Full logging. Minimal logging directly to the data page then writes only a pointer to the datapage in the transaction log, while Full logging writes the content of all the rows to the transaction log prior to inserting them into the data page. Needless to say, in order to take advantage of quick inserts, you will want to employ minimal logging. There are however a few prerequisites. The database recovery model of the target table must be either Simple or Bulk Logged If the target table contains a clustered index, it cannot contain data A table lock must be aquired on the target table The table cannot be part of a replication scheme If the table contains a non clustered index, the index itself will be […]
SQL Server Denali has a new feature allowing pagination using the order by clause. A common solution needed for the front end is to paginate records prior to sending them to the webserver. More frequently now, we are seeing demormalized data sets being stored in the WebServer’s or a middle tiers cache mechanism. Those solutions however are more difficult to maintain, persist and synchronize. Enter the old fashioned database paging solution. This paging solution initially grabs a subset of a table and counts the records. It then stores ordered results based on the parameter passed into the common table expression. Additional parameters are the number of rows the caller wants on each page and the page number the caller is currently retrieving. [cc lang=”sql”] CREATE PROCEDURE dbo.GetEmployees ( @SortColumn VARCHAR(50) = null, @iRows INT = 10, @iPageNum INT = 1 ) AS BEGIN SET NOCOUNT ON DECLARE @RecordCount int DECLARE @iNbrPages int SET @RecordCount = 0 SET @iNbrPages = 0 SELECT emp.EmployeeID, emp.FirstName, emp.LastName, emp.DateHired INTO #Employees FROM HR.Employees emp WHERE emp.IsTerminated = 1 SELECT @iNbrPages = CEILING(COUNT(1)/(@iRows*1.0)), @RecordCount = COUNT(1) FROM #Employees BEGIN ;WITH PagingCTE ( Row_ID, EmployeeID, FirstName, LastName, DateHired ) AS ( SELECT ROW_NUMBER() OVER(ORDER BY CASE WHEN @SortColumn = ‘EmployeeID’ THEN emp.EmployeeID END ASC, CASE WHEN @SortColumn = ‘FirstName’ THEN emp.FirstName END ASC, CASE WHEN @SortColumn = ‘LastName’ THEN emp.LastName END ASC, CASE WHEN @SortColumn = ‘DateHired’ THEN emp.DateHired END ASC ) AS [Row_ID], emp.EmployeeID, emp.FirstName, emp.LastName, emp.DateHired FROM #Employees emp ) SELECT emp.EmployeeID, emp.FirstName, emp.LastName, […]
Continue reading ...
