This is a useful script if you want to turn a specific list of jobs that are currently enable to a disabled status and vice versa. Just specify the list of job names in the VALUES section below: [cc lang=”sql”] use msdb go IF OBJECT_ID(N’tempdb..#jobs’) IS NOT NULL BEGIN DROP TABLE #jobs END CREATE TABLE #jobs ( job_name nvarchar(max), ) INSERT INTO #jobs(job_name) VALUES (‘Job Name 1 here’), (‘Job Name 2 here’), (‘Run index selection Job’) IF OBJECT_ID(N’tempdb..#job_status’) IS NOT NULL BEGIN DROP TABLE #job_status END SELECT ID = identity(int, 1,1), [job_name], enabled INTO #job_status FROM [msdb].[dbo].[sysjobs] AS [sJOB] JOIN #jobs j ON j.job_name = sJOB.name DECLARE @job_name nvarchar(max) = ”; DECLARE @enabled int; DECLARE @iter int = (SELECT MAX(ID) FROM #job_status); WHILE @iter > 0 BEGIN SET @job_name = ” SELECT @job_name = job_name, @enabled = CASE WHEN [enabled] = 0 THEN 1 ELSE 0 END FROM #job_status WHERE ID = @iter; EXEC msdb.dbo.sp_update_job @job_name = @job_name, @enabled = @enabled ; PRINT cast(@job_name as varchar) + ‘:’ + cast(@enabled as varchar) SET @iter -= 1; END [/cc]
Continue reading ...
This is a great DMV if you don’t want to leave SSMS to see the CPU usage or if you don’t have access. What’s good about this method is it also shows you the history of the cpu usage. [cc lang=”sql”] declare @ts_now bigint select @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) from sys.dm_os_sys_info; select record_id, dateadd(ms, -1 * (@ts_now – [timestamp]), GetDate()) as EventTime, SQLProcessUtilization, SystemIdle, 100 – SystemIdle – SQLProcessUtilization as OtherProcessUtilization from ( select record.value(‘(./Record/@id)[1]’, ‘int’) as record_id, record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]’, ‘int’) as SystemIdle, record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]’, ‘int’) as SQLProcessUtilization, timestamp from ( select timestamp, convert(xml, record) as record from sys.dm_os_ring_buffers where ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’ and record like ‘%%’) as x ) as y order by record_id desc [/cc]
This gem of a query has been indispensable in determining IO latency for our servers. The benefit of this is not having to rely on perfmon to interpret the latency (which can be a crapshoot). Here, SQL itself using SQL OS (I believe) to measure latency and usage statistics. In the past, perfmon has been sketchy in monitoring physical disk stats because it is not reliable when measuring a SAN. The old trick was to make sure perfmon’s refresh interval was below 2 second refresh in order to get good statistics. Now with this, we can see the exact latency of read / writes against the actual files. Thanks to my partner Chris for providing this. [cc lang=”sql”] SELECT DB_NAME(fs.database_id) AS [Database Name], mf.physical_name, io_stall_read_ms, num_of_reads, CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms, num_of_writes, CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms], io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io], CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms] FROM sys.dm_io_virtual_file_stats(null,null) AS fs INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id] ORDER BY avg_io_stall_ms DESC; [/cc]
Continue reading ...
Over the years I’ve ran across certain situations that cause errors in SQL that error out the calling application if they are not correctly trapped. As unsuspecting as these can be, you end up learning your lesson when you get a call late at night that a web page is erroring out. Then to follow you get a deep sinking feeling in your gut while you think, “Why didn’t I think of that?”. In some of these cases I’ve often wondered why SQL doesn’t simply return a NULL or an invalid result. And often I wish there was an option that could set that because while it’s true that all errors should be handled, in truth they are not. Hopefully this list can save you a bit of heartache. divide by zero string or binary data would be truncated Invalid length parameter passed to the LEFT or SUBSTRING function These three errors are very common and all of them at one time or another have bit me in the pants – most of the time after deploying code and even worse, hours/days/months after deploying the code. Not only that, they can occur on an intermittent basis which is always fun to troubleshoot. Divide by Zero This innocent division operation turns ugly when your divisor hits the dead even zero. This is because of the rules of math. You simply cannot divide by zero. Reproducing this issue is simple enough. [cc lang=”sql”] SELECT 8 / 0 [/cc] The workaround is to […]
In the troubleshooting guide we went over the different physical bottlenecks that can cause SQL Server slowness and showed how to identify them. In this guide, we’ll drill down one step deeper to help identify the actual settings or queries that are contributing to the bottleneck. By relieving these bottlenecks we will start to see our batch requests / sec increase. While it may seem you will be able to go through this article once, what is more likely is that you will need to continue to iterate through some of these principles multiple times. This is because many times when one bottleneck is relieved, another is revealed. One thing we will not cover much in this article is architecture. Unfortunately this is a much more complex subject. Suffice it to say, either you have it or you don’t. I can’t so much give architecture principles in an optimization guide, but it is probably the single most important factor in determining throughput in a system. Without a good foundation you can only build a house so large. With that said, there are still luckily plenty of optimization techniques we can employ. Missing Indexes The biggest way to be a hero in your organization is to create the missing indexes. This task is simple ever since the introduction of DMV’s. When an index is missing it contributes directly to IO usage, and the higher the IO typically means higher CPU. Each time the SQL Server service is restarted the system tables […]
Continue reading ...
