What to do when your SQL CPU is at 100%

We’ve all had it happen, alerts start going off that the CPU on the SQL Server is now pegged. It’s an all hands on deck situation that you need to figure out quick. It helps to know the history of your SQL Server performance, but if you don’t there’s still hope using some troubleshooting techniques. First Make sure it’s SQL Server Sometimes there can be other processes (even backups) that are causing the CPU to push over that final edge. If you don’t have access to log into the box, the quickest way to determine how much CPU SQL Server is using is to run the following DMV: 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('(.[email protected])[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 ‘%<SystemHealth>%’) AS x ) AS y ORDER BY record_id DESC; If the OtherProcessUtilization column has a high value then you know you need to log into the box, open task manager and figure out what is hosing your system. It […]

Continue reading ...

Export Table to CSV File – step by step

A very common requirement with SQL Server is to export a table to a csv file. It’s actually an easy task when using SQL Server Management Studio (SSMS). The export method actually behind the scenes uses SSIS wizard which also gives you an option to save the package that performs the export, however saving the package is often not necessary. The permissions you need to perform this, are merely read permissions on the table. Here are the steps broken out in detail. Right-click on the database that contains the table and choose tasks -> Export Data… Click next past the intro screen and on the “Choose a Data Source Screen” make sure the database that contains your table is listed in the drop down. Then Select Next. On the “Choose a Destination” Window, select “Flat File Destination” in the top drop down box labeled “Destination”. On the same “Choose a Destination” Window, click the “Browse” button and the Open file dialog box appears prompting you to find a location for your file. Find the location, then type in the file name. THEN to the right of the file name text box, change the drop down from “Text Files (*.txt)” to “CSV Files (*.csv)” As shown below. While still on the same “Choose a Destination” window, select whether you want the column names to be included in the CSV by selecting the “Column names in first data row” check box. Then Select Next. On the “Specify Table Copy or Query” window […]

Continue reading ...

Search Procedure Text

This has to be one of the most common repetitive functions needed for development. Updating a certain column in a specific table and want to find all the procedures that are affected by that? You’ll need to use an object search. There are a few different methods for doing this. The ANSI standard method using the INFORMATION_SCHEMA.ROUTINES system views have not in the past contained all of the stored procedure text. Only the first 8000 characters or so. (If I’m wrong on that, don’t challenge me because I don’t care). Simple snippet SELECT sm.*, so.* FROM sys.sql_modules sm WITH (NOLOCK) INNER JOIN sys.objects so WITH (NOLOCK) ON sm.[object_id] = so.[object_id] WHERE sm.definition LIKE ‘%table_name%’ However if you’re going to be using this search a lot, and I can’t imagine you’re not, then why not encapsulate it in a stored procedure so you can include multiple terms, exclude terms, and sort. You will have to place this procedure in each database you want to search however. Stored Procedure: sp_search USE master GO CREATE PROCEDURE spsearch     @include VARCHAR(MAX) = NULL,     @sort VARCHAR(5) = 1, — 1 =’name_asc’ 2 = ‘name_desc’ 3 = ‘date_desc’, 4 = ‘date_asc’     @exclude VARCHAR(MAX) = NULL     AS — EXEC sp_search ‘UPDATE,products,status’, 3 BEGIN     SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;     DECLARE @Delimiter VARCHAR(MAX) = ‘,’     IF OBJECT_ID(N’tempdb..#split’) IS NOT NULL DROP TABLE #split     ;WITH split(stpos,endpos)     AS(         SELECT […]

Continue reading ...

Copy Stored Procedures Between Servers

This procedure will migrate all your stored procedures located on on server to another via linked server. Just specify the linked server name, remote database, and local database. CREATE PROCEDURE [dba].[pr_refresh_create_procedures]     @linked_server VARCHAR(100),     @source_db VARCHAR(100),     @target_db VARCHAR(100) AS –EXEC [dba].[pr_refresh_create_procedures] ‘LOCALSERVER’, ‘yourdbname’, ‘targetdb_dev’ SET @source_db = ‘[‘ + @linked_server + ‘].[‘ + @source_db + ‘]’; DECLARE @SQL Nvarchar(MAX) DECLARE @Name VARCHAR(255) = @target_db IF OBJECT_ID(N’admindb.tmp.shelldb_copy_procedures’) IS NOT NULL DROP TABLE admindb.tmp.shelldb_copy_procedures SET @SQL = ‘SELECT p.name, m.Definition INTO admindb.tmp.shelldb_copy_procedures FROM ‘ + @source_db + ‘.sys.objects p WITH (NOLOCK) INNER JOIN ‘ + @source_db + ‘.sys.sql_modules m WITH (NOLOCK) ON p.object_id = m.object_id WHERE type IN (”FN”, ”IF”, ”TF”, ”P”, ”V”, ”TT”)’ EXEC(@SQL) DECLARE c CURSOR FOR    SELECT Definition    FROM admindb.tmp.shelldb_copy_procedures OPEN c FETCH NEXT FROM c INTO @SQL WHILE @@FETCH_STATUS = 0 BEGIN    SET @SQL = REPLACE(@SQL,””,”””)    SET @SQL = N’execute ‘ + QUOTENAME(@name) + N’.dbo.sp_executesql N”’ + @SQL + ””    EXEC(@SQL)    FETCH NEXT FROM c INTO @SQL END             CLOSE c DEALLOCATE c

Continue reading ...

Copy Table Data Between Servers

This script will copy table data from one environment to another. This script uses a multithreaded clr procedure created by Alan Kaplan here. You can also change the parallel call below if you want to execute serially. CREATE PROCEDURE [dba].[pr_refresh_insert_table_data]     @linked_server VARCHAR(100),     @source_db VARCHAR(100),     @target_db VARCHAR(100) AS — dba.pr_refresh_insert_table_data ‘’, ‘yourdbname’, ‘targetdb’ — SELECT * FROM admindb.dbo.refresh_table_columns BEGIN     SET NOCOUNT ON;     DECLARE @SQL nvarchar(MAX);     DECLARE @SQLColumnList nvarchar(MAX);     DECLARE @SQLSelectColumnList nvarchar(MAX);     DECLARE @CRLF nvarchar(2);     DECLARE @Tab nvarchar(1);     DECLARE @RecordKeyName nvarchar(50);     DECLARE @TableID INT;     DECLARE @TableCount INT;     DECLARE @SOURCE VARCHAR(MAX) = ‘[‘ + @linked_server + ‘].[‘ + @source_db + ‘]’     DECLARE @TableName VARCHAR(255);     SET @TableName = ”; — Comment out to use list technique below     DECLARE @TableList TABLE (         TableID INT IDENTITY(1, 1) NOT NULL,         TableName VARCHAR(255)         );         DECLARE @InsertData Generic_Tt_VarcharMax_5_Col     INSERT INTO @TableList(TableName)     SELECT DISTINCT TABLE_NAME     FROM admindb.[tmp].[refresh_table_schema_list] tsl     WHERE process_data = 1     AND EXISTS (SELECT 1 FROM admindb.tmp.refresh_create_tables WHERE TABLE_NAME = tsl.TABLE_NAME)     SET @CRLF = CHAR(13) + CHAR(10);     SET @Tab = CHAR(9);       SET @TableCount = (SELECT COUNT(1) FROM @TableList);     SET @TableID = 1; DECLARE @has_identity INT     SET @TableID = 1;   […]

Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!