Monday, 17 December 2018
Friday, 3 August 2018
Manually Checking Table Statistics
How to examine table statistics and update them if necessary
UPDATE STATISTICS dbo.Study WITH FULLSCAN DBCC SHOW_STATISTICS ('dbo.study','pk_study') SELECT COUNT(*) AS [RowsInTable] FROM dbo.study
Tuesday, 31 July 2018
findInName - Stored Procedure to search all object names
-search all databases CREATE OR ALTER PROC tools.findInName (@searchstring VARCHAR(200)) AS BEGIN SET NOCOUNT ON DECLARE @results TABLE ( [search_term] VARCHAR(255), [database_name] VARCHAR(255), [schema_name] VARCHAR(255), [object_Name] VARCHAR(255), [type_desc] VARCHAR(255) ) DECLARE @dbname sysname; DECLARE @actualsearchstring VARCHAR(200); SET @actualsearchstring = REPLACE(@searchstring,'_','/_') DECLARE @dsql nvarchar(max) = ' SELECT ''' +@searchstring + ''' AS [search_term] ,DB_NAME() AS [database_name] ,TABLE_SCHEMA ,TABLE_NAME ,TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ''%'+@actualsearchstring+'%'' ESCAPE ''/'' UNION ALL SELECT ''' +@searchstring + ''' AS [search_term] ,DB_NAME() AS [database_name] ,ROUTINE_SCHEMA ,ROUTINE_NAME ,ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME LIKE ''%'+@actualsearchstring+'%'' ESCAPE ''/'' '; DECLARE @execCommand nvarchar(max); DECLARE dbs CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY FOR SELECT name FROM sys.databases (NOLOCK) OPEN dbs; FETCH NEXT FROM dbs INTO @dbname; WHILE @@FETCH_STATUS = 0 BEGIN SET @execCommand = 'EXEC ' + QUOTENAME(@dbname) + '.sys.sp_executesql @dsql'; INSERT @results EXEC sys.sp_executesql @execCommand, N'@dsql nvarchar(max)', @dsql FETCH NEXT FROM dbs INTO @dbname; END CLOSE dbs; DEALLOCATE dbs; SELECT * FROM @results END GO EXEC tools.findInName '_'
TSQL - Searching for Escaped Characters
Use the ESCAPE keyword to define a character to use to escape a literal.
The _ underscore cannot usually be searched, to do so escape it like this.
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%/_%' ESCAPE '/'
findInCode - Stored Procedure to search code in all databases for a string
--search all databases CREATE OR ALTER PROC tools.findInCode (@searchstring VARCHAR(200)) AS BEGIN SET NOCOUNT ON DECLARE @results TABLE ( [searchTerm] VARCHAR(255) ,[databaseName] VARCHAR(255) ,[schemaName] VARCHAR(255) ,[objectName] VARCHAR(255) ,[typeDesc] VARCHAR(255) ) DECLARE @dbname sysname; DECLARE @dsql nvarchar(max) = ' SELECT ''' +@searchstring + ''' AS [search_term] ,DB_NAME() AS [databaseName] ,SCHEMA_NAME(schema_id) as schemaName ,o.name AS ObjectName ,o.type_desc AS typeDesc FROM sys.sql_modules m (NOLOCK) INNER JOIN sys.objects o (NOLOCK) ON m.object_id = o.object_id WHERE m.definition LIKE ''%'+@searchstring+'%'' '; DECLARE @execCommand nvarchar(max); DECLARE dbs CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY FOR SELECT name FROM sys.databases (NOLOCK) OPEN dbs; FETCH NEXT FROM dbs INTO @dbname; WHILE @@FETCH_STATUS = 0 BEGIN SET @execCommand = 'EXEC ' + QUOTENAME(@dbname) + '.sys.sp_executesql @dsql'; INSERT @results EXEC sys.sp_executesql @execCommand, N'@dsql nvarchar(max)', @dsql FETCH NEXT FROM dbs INTO @dbname; END CLOSE dbs; DEALLOCATE dbs; SELECT * FROM @results END GO EXEC tools.findInCode 'findme'
Friday, 27 July 2018
Database Mirroring : TSQL Health Scripts
-- Dynamic Managment View of configuration information SELECT db_name(sd.[database_id]) AS [Database Name] ,sd.mirroring_state AS [Mirror State] ,sd.mirroring_state_desc AS [Mirror State] ,sd.mirroring_partner_name AS [Partner Name] ,sd.mirroring_role_desc AS [Mirror Role] ,sd.mirroring_safety_level_desc AS [Safety Level] ,sd.mirroring_witness_name AS [Witness] ,sd.mirroring_connection_timeout AS [Timeout(sec)] FROM sys.database_mirroring AS sd WHERE mirroring_guid IS NOT null ORDER BY [Database Name];
-- history of all mirored transactions in last 2 hours USE msdb; EXEC sp_dbmmonitorresults DatabaseName, 2, 0;
--os performance counters about mirroring SELECT *,[counter_name] as CounterName,[cntr_value] as CounterValue FROM sys.dm_os_performance_counters WHERE [object_name] LIKE ('%Database Mirroring%') AND [instance_name]='mirroredDatabase'
Sunday, 22 July 2018
Linked Server Setting - Promotion of Distributed Transactions
This error occured when I tried to access a stored proedure via a linked server.
Getting the results worked fine, simply executing the stored procedure.
But putting the results into a temporary table meant it became a 'distributed transaction'
OLE DB provider "SQLNCLI10" for linked server "SERVERNAME" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 12
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVERNAME" was unable to begin a distributed transaction.
Both servers were already running the Distributed Transaction Coordinator (DTC) service.
Multiple posts suggest tuning the security of DTC, but I eventually found the solution was much simpler. Like the setting for Remote Procedure calls RPC, it turned out to be a property of the linked server itself, 'Enable Promotion of Distributed Transactions'.
The default for 'Enable Promotion of Distributed Transactions' is TRUE, setting it to FALSE enabled my code to run.
Getting the results worked fine, simply executing the stored procedure.
EXEC [SERVERNAME].[DatabaseName].[SchemaName].[myStoredProcedure]
But putting the results into a temporary table meant it became a 'distributed transaction'
CREATE TABLE ##tempTable (column1, column2...) INSERT INTO ##tempTable EXEC [SERVERNAME].[DatabaseName].[SchemaName].[myStoredProcedure]
OLE DB provider "SQLNCLI10" for linked server "SERVERNAME" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 12
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVERNAME" was unable to begin a distributed transaction.
Both servers were already running the Distributed Transaction Coordinator (DTC) service.
Multiple posts suggest tuning the security of DTC, but I eventually found the solution was much simpler. Like the setting for Remote Procedure calls RPC, it turned out to be a property of the linked server itself, 'Enable Promotion of Distributed Transactions'.
The default for 'Enable Promotion of Distributed Transactions' is TRUE, setting it to FALSE enabled my code to run.
Thursday, 19 July 2018
Renaming a Linked Server
Simple but very helpful as scripting out a linked server won't include the password information.
EXEC master.dbo.sp_serveroption @server=N'OLDNAME', @optname=N'name', @optvalue=N'NEWNAME'
Sunday, 8 July 2018
Trace Flag 7470
Trace flag 7470 works in SQL 2017! I'm always a little reluctant to use trace flags, especially after reading that Trace flags were becoming redundant due to more database level settings.
Anyway, I've eliminated the Sort operator spilling to tempdb by applying 7470.
Note, the documentation says it doesn't apply to SQL 2017, but I had it on good authority that it would,
Trace Flag 7470 : Fix operator spills to tempdb
Anyway, I've eliminated the Sort operator spilling to tempdb by applying 7470.
Note, the documentation says it doesn't apply to SQL 2017, but I had it on good authority that it would,
Trace Flag 7470 : Fix operator spills to tempdb
Sunday, 3 June 2018
TSQL : HEAP table usage script
This script reports usage of HEAP tables over a 15 minute period.
It reports reads, writes and row counts for the tables.
I wrote it as I found a system exhibiting high values for Performance Counter 'forwarded records/sec'.
See Also : TSQL : HEAPS and forwarded records
It reports reads, writes and row counts for the tables.
I wrote it as I found a system exhibiting high values for Performance Counter 'forwarded records/sec'.
See Also : TSQL : HEAPS and forwarded records
IF OBJECT_ID('tempdb..#Snap1') IS NOT NULL DROP TABLE #Snap1 IF OBJECT_ID('tempdb..#Snap2') IS NOT NULL DROP TABLE #Snap2 IF OBJECT_ID('tempdb..#rowcounts') IS NOT NULL DROP TABLE #rowcounts SELECT s.object_id , OBJECT_SCHEMA_NAME(s.object_id) AS schemaname , OBJECT_NAME(s.object_id) AS tablename , i.name AS indexname , SUM(user_seeks) user_seeks, SUM(user_scans) user_scans, SUM(user_lookups) user_lookups, reads = SUM(user_seeks + user_scans + user_lookups), writes = SUM(user_updates) INTO #snap1 FROM sys.dm_db_index_usage_stats AS s INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND i.index_id = s.index_id WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1 AND OBJECTPROPERTY(s.object_id,'TableHasClustIndex') = 0 -- Reporting only on HEAPS GROUP BY s.object_id,i.name WAITFOR DELAY '00:15:00' SELECT s.object_id , OBJECT_SCHEMA_NAME(s.object_id) AS schemaname , OBJECT_NAME(s.object_id) AS tablename , i.name AS indexname , SUM(user_seeks) user_seeks, SUM(user_scans) user_scans, SUM(user_lookups) user_lookups, reads = SUM(user_seeks + user_scans + user_lookups), writes = SUM(user_updates) INTO #snap2 FROM sys.dm_db_index_usage_stats AS s INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND i.index_id = s.index_id WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1 AND OBJECTPROPERTY(s.object_id,'TableHasClustIndex') = 0 -- Reporting only on HEAPS GROUP BY s.object_id,i.name SELECT sys_schemas.name AS schemaName ,sys_objects.name AS tableName ,sys_indexes.name AS indexName ,SUM(partition_stats.used_page_count) * 8 AS indexsizekb ,MAX(partitions.rows) AS tablerows INTO #rowcounts FROM sys.dm_db_partition_stats partition_stats INNER JOIN sys.indexes sys_indexes ON partition_stats.[object_id] = sys_indexes.[object_id] AND partition_stats.index_id = sys_indexes.index_id INNER JOIN sys.objects sys_objects ON sys_objects.[object_id] = partition_stats.[object_id] INNER JOIN sys.schemas sys_schemas ON sys_objects.[schema_id] = sys_schemas.[schema_id] AND sys_schemas.name <> 'SYS' INNER JOIN sys.partitions partitions ON partitions.object_id = partition_stats.object_id WHERE OBJECTPROPERTY(sys_objects.object_id,'TableHasClustIndex') = 0 GROUP BY sys_schemas.name ,sys_objects.name ,sys_indexes.name ORDER BY sys_schemas.name ,sys_objects.name ,sys_indexes.name SELECT i.schemaname ,i.tablename ,i.indexname ,(j.user_seeks - i.user_seeks) AS seeks_period ,(i.user_scans - i.user_scans) AS scans_period ,(j.user_lookups - i.user_lookups) AS lookups_period ,(j.reads - i.reads) AS reads_period ,(j.writes - i.writes) AS writes_period ,r.tablerows ,r.indexsizekb FROM #Snap1 i INNER JOIN #Snap2 j ON i.tablename = j.tablename INNER JOIN #rowcounts r ON r.tableName = i.tablename AND r.indexName = i.indexname AND COALESCE(i.indexname,'!') = COALESCE(j.indexname,'!')
TSQL : HEAPS and Forwarded Records
A little bemused that I'm still finding this in application code in the 21st Century, but there you go.
A live app experiencing performance problems due to a significant number of heaps, each frequently used.
SELECT OBJECT_NAME(stats.object_id) as [Table], idx.name as [Index], stats.index_type_desc, stats.page_count, stats.avg_fragmentation_in_percent, stats.forwarded_record_count FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') AS stats INNER JOIN sys.indexes AS idx (NOLOCK) ON stats.OBJECT_ID = idx.OBJECT_ID AND stats.index_id = idx.index_id WHERE forwarded_record_count > 0 ORDER BY forwarded_record_count descSee also : HEAP table usage script
Friday, 1 June 2018
TSQL : Clustered Indexes, Identities & Primary Keys
I'm Currently looking at a large database where there are
- HEAPS (tables without a clustered index)
- IDENTITY columns that could serve as Primary Keys (but aren't)
- PRIMARY KEYS that have been defined as non-clustered (someone has scripted them out and ignored the default clustered status of a PK).
Anyway, this is coming in useful ...
SELECT SCHEMA_NAME(schema_id) AS SchemaName ,name AS TableName ,OBJECTPROPERTY(object_id,'TableHasClustIndex') HasClusteredIndex ,OBJECTPROPERTY(object_id,'TableHasIdentity') HasIdentity ,OBJECTPROPERTY(object_id,'TableHasprimarykey') HasPrimaryKey FROM sys.tables WHERE OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0 ORDER BY 1, 2
Friday, 18 May 2018
SQLTips : CONCAT
The CONCAT function is available from SQL 2016+.
It can save you a lot of messing around with adding strings together and coping with NULLS and empty strings.
SELECT CONCAT('The cat sat',' ', 'on', ' ', 'the mat') GO SELECT CONCAT(NULL, ' and void') GO DECLARE @int int = 99 DECLARE @varchar varchar(13) = ' red balloons' SELECT CONCAT(@int,@varchar) GO DECLARE @varchar varchar(20) = 'The date today is ' DECLARE @dt datetime = GETDATE() SELECT CONCAT(@varchar,@dt) GO
Tuesday, 15 May 2018
sp_ms_marksystemobject
On searching for a table I found it to be hidden. Turns out it was marked as 'ms shipped' i.e was a system object.
To achieve this yourself you can use sp_ms_marksystemobject
select * from sys.tables WHERE name = 'sysssislog'
To achieve this yourself you can use sp_ms_marksystemobject
EXEC sp_ms_marksystemobject 'dbo.sysssislog'
Monday, 9 April 2018
Tables with Computed Columns
Tables with Computed Columns
and without
SELECT SCHEMA_NAME(schema_id) AS schemaname , t.name as tablename , cc.name , cc.definition FROM sys.tables t INNER JOIN sys.Computed_columns cc ON t.object_id = cc.object_id
and without
select SCHEMA_NAME(schema_id) AS schemaname , t.name as tablename from sys.tables t WHERE object_id not in (SELECT object_id FROM sys.Computed_columns)
Tuesday, 20 March 2018
Suspect Database - Steps to Recovery
For reference, the steps I took to (successfully) recover a 2008 database that was marked as Suspect in Management Studio.
Run each separately, and review the results...
EXEC sp_resetstatus 'mySuspectDB'
ALTER DATABASE mySuspectDB SET EMERGENCY
DBCC CHECKDB('mySuspectDB')
ALTER DATABASE mySuspectDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB ('mySuspectDB', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE mySuspectDB SET MULTI_USER
Run each separately, and review the results...
EXEC sp_resetstatus 'mySuspectDB'
ALTER DATABASE mySuspectDB SET EMERGENCY
DBCC CHECKDB('mySuspectDB')
ALTER DATABASE mySuspectDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB ('mySuspectDB', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE mySuspectDB SET MULTI_USER
Friday, 16 February 2018
HASHBYTES and FOR XML to create a binary checksum
SELECT TOP (1000) [Id] ,[AboutMe] ,[Age] ,[CreationDate] ,[DisplayName] ,[DownVotes] ,[EmailHash] ,[LastAccessDate] ,CHKSUM = CONVERT(VARBINARY(20),HASHBYTES('MD5', (SELECT s.* FROM (VALUES(NULL))Foo(Bar) FOR XML AUTO, BINARY BASE64))) FROM [StackOverFlow2010].[dbo].[Users] s
Thursday, 4 January 2018
Mail process taking forever Suspended Process - msdb.dbo.sp_readrequest;1
Finding a process taking forever in the suspended state, I wondered what it could be.
Fortunately Irina Tudose had already done the hard work for me. It is a process used by the mail procedures in msdb. She recommends changing the default value for DatabaseMailExeMinimumLifeTime to resolve this.
See also
SQL Server: Why a Session With sp_readrequest Takes so Long to Execute
UPDATE msdb.dbo.sysmail_ configuration
SET paramvalue = 60 --60 Seconds
WHERE paramname = ' DatabaseMailExeMinimumLifeTime '
Subscribe to:
Posts (Atom)