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: [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] 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 should be noted that you really should only run SQL Server on SQL Boxes, and if this is your issue, you’ll learn that today. If it is SQL Server, then it typically falls into one […]
Continue reading ...
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 ...
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 [cc lang=”sql”] 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%’ [/cc] 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 [cc lang=”sql”] 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 0 AS stpos, CHARINDEX(@delimiter,@include) AS endpos UNION ALL SELECT CAST(endpos+1 as int), CHARINDEX(@delimiter,@include,endpos+1) FROM split WHERE endpos […]
Continue reading ...
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. [cc lang=”sql”] 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 [/cc]
Continue reading ...
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. [cc lang=”sql”] 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 ‘10.10.60.58’, ‘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; WHILE @TableID
Continue reading ...
