Friday, April 06, 2018

Handy - SQL Scripts to show high CPU usage

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.

— 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

Tuesday, November 28, 2017

Power BI - Airborne


So we’ve introduced Airborne, a 3-step service which helps you create a tailored business intelligence solution, no matter your size, budget or needs. check it out https://lnkd.in/edurEGC

Wednesday, August 17, 2016

Extremely slow log shipping restore in standby mode ...


Recently I was working on an environment where the requirement was to use a log shipped secondary database for reporting purposes. The catch here was that the restore of all the transaction log backups needed to complete within a fixed time.
I understand that the restore of transaction logs are dependent on a number of parameters like disk performance, number of transactions present in the log backup, resource availability on the server where the restore is occurring, But considering that all the factors remain constant, there is a basic difference between a secondary log shipped database in No Recovery and in Standby mode. In Standby mode, a partial recovery is done with the help of a transaction undo file to get the database into a read-only state. This partial recovery operation and undo that occurs while performing the next log restore (with the use of the transaction undo file) requires time and resources and can slow down the time of restore for each transaction log backup that is restored on the secondary database, especially if there are resource contention issues on the server.
There can be a significant amount of time saved when operating usingnorecovery as compared to standby mode for log shipping. Since it is not supported to directly modify the log shipping jobs, you can create your own job which executes the necessary T-SQL commands and invokes the necessary log shipping jobs in the correct order.
The correct flow of events would be:
1. Change log shipping restore mode to norecovery using the stored procedure sp_change_log_shipping_secondary_database
2. Start the log shipping restore job
3. Change the log shipping restore mode to standby using the stored procedure sp_change_log_shipping_secondary_database
4. Initiate the log backup job on the primary server
5. Initiate the log copy job
6. Initiate the restore job in the same order to ensure that the new restore mode is affected as an operating mode changes only after a new transaction log backup is restored.


Tuesday, February 09, 2016

Free SQL eBooks on 2016

Just a heads-up that Microsoft have published 2 new free eBooks for 2016 as follows:
-          Introducing MS SQL 2016
-          Enterprise Cloud Strategy

They can be downloaded from the following location: