Overcoming Issues with TempDB Contention

One of the possible bottlenecks for a busy SQL Server is contention in TempDB. This symptoms of this bottleneck are mostly excess wait_types of PAGELATCHEX that are blocking one another. (you can use sp_who3 in order to see whether those wait types are popping up during your busy times). The cause of this is either implicit or explicit use of TempDB. Explicit being creation of table variables and temp tables, or implicit being worktables being generated by complex execution plans. Luckily there are a number of steps you can take in order to overcome these issues. 1) Enable traceflag 1118 There is some argument as to whether this trace flag actually makes a difference. first of all, MS SQL CSS engineers state it’s already the default for 2008. What the flag does is immediately allocate space for tempdb tables upon creation rather than on insert (if I understand it correctly). It did seem to help our database during one point of contention, but I’m not willing to test it again by turning it off. So with that said, “it can’t hurt to turn it on” at least that’s that the consensus of what I’ve read. 2) Put TempDB on a PCIE SSD The PCIE cards are getting cheaper every month. Just putting this in, reduced our write speed from 2.3ms to .3ms. What that translates into is, it’s quicker to get “in and out” of tempdb allowing less contention and higher throughput. Also while I’m too lazy to prove it, […] Continue reading ...

CTE’s vs Temp tables – an Opinion

This is one of those topics that will get people fired up. But here goes. I am mostly an explicit temp table developer. By contrast, I am not an implicit temp table developer. What I mean by that is – in writing SQL, you simply cannot avoid the use of tempdb. Either you write to tempdb by breaking queries out and intentionally create temp tables, or you write to tempdb by not breaking queries out (keeping them as long convoluted statements with a long convoluted query plan) and let the optimizer create “worktables”. In either case you are writing to tempdb whether you like it or not. Yet.. the difference is this: Breaking them out: You can control the size of the result set being written to disk You can ensure that the execution plan is simple You can utilize the materialized temp table data throughout the entire procedure Temp tables contain statistics and can be indexed To compare temp table development to CTE development is somewhat of an apples and oranges comparison. A CTE uses nothing special on the back end. It is simply a (potentially) clean way to write a query. The difference is this however. With a CTE, the execution plan of the main query becomes intertwined with the CTE, leaving more room for the optimizer to get confused. By contrast, when a temp table divides two queries, the optimizer is not intertwined with that which created the temp table and the execution plans stay simple and […]
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. [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 ...

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. [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 ...

Copy Table DDL Between Servers

This script will allow you to specify a linked server name, a remote database on that linked server, and a local database name in which to copy all the tables from on server onto another. [cc lang=”sql”] CREATE PROCEDURE [dba].[pr_refresh_create_tables] @linked_server varchar(100), @source_db varchar(100), @target_db varchar(100), @debug_mode int = 0 AS — dba.pr_refresh_create_tables ‘10.10.60.58’, ‘yourdbname’, ‘targetdb’, 0 — SELECT * FROM admindb.dbo.refresh_table_columns BEGIN 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 @RecordKeyDataType nvarchar(50); DECLARE @TableID int; DECLARE @TableCount int; DECLARE @source varchar(max) = ‘[‘ + @linked_server + ‘].[‘ + @source_db + ‘]’ DECLARE @TableName varchar(255); DECLARE @TableList TABLE ( TableID int IDENTITY(1, 1) NOT NULL, TableName varchar(255) ); IF OBJECT_ID(N’admindb.dbo.refresh_table_columns_tmp’) IS NOT NULL DROP TABLE admindb.dbo.refresh_table_columns_tmp IF OBJECT_ID(N’admindb.dbo.refresh_table_columns’) IS NOT NULL DROP TABLE admindb.dbo.refresh_table_columns IF OBJECT_ID(N’admindb.tmp.refresh_create_tables’) IS NOT NULL DROP TABLE admindb.tmp.refresh_create_tables CREATE TABLE admindb.dbo.refresh_table_columns_tmp ( RowID int NOT NULL, TableName varchar(255), ColumnName varchar(255), IsIdentity bit, DataType varchar(50), IsDataTypeCast bit, IsNullable bit, IsLast bit ); CREATE TABLE admindb.tmp.refresh_create_tables ( ID INT identity(1,1), table_name varchar(255), create_table varchar(max) ) SET NOCOUNT ON; SET @CRLF = CHAR(13) + CHAR(10); SET @Tab = CHAR(9); SET @sql = ‘INSERT INTO admindb.dbo.refresh_table_columns_tmp ( RowID, TableName, ColumnName, IsIdentity, DataType, IsDataTypeCast, IsNullable ) SELECT RowID = ROW_NUMBER() OVER(PARTITION BY t.TableName ORDER BY t.IsIdentity DESC, t.ORDINAL_POSITION), TableName, ColumnName, IsIdentity, DataType, IsDataTypeCast, IsNullable FROM ( SELECT DISTINCT TableName = c.TABLE_NAME, ColumnName = c.COLUMN_NAME, IsIdentity = sc.is_identity, DataType = ”” + CASE WHEN LOWER(c.DATA_TYPE) IN (”numeric”, ”decimal”, ”real”) THEN LOWER(c.DATA_TYPE) […] Continue reading ...
  1. Pages:
  3. 1
  4. 2
  5. 3
  6. 4
  7. 5
  8. 6
  9. 7
  10. ...
  11. 55

Featured Articles

 Site Author

  • Thanks for visiting!

Resources

css.php