Tuesday, 8 April 2014

Update Out Of Date SQL Server Statistics Query

--The following script updates all out of date statistics.
--Set the @MaxDaysOld variable to the number of days you will allow the
--statistics to be out of date by. Setting the @SamplePercent variable to null
--will use the SQL Server default value of 20,000 rows. You can also change
--the sample type to specify rows or percent.
--Taken from http://sqlserverplanet.com/dba/update-statistics


DECLARE @MaxDaysOld INT
DECLARE @SamplePercent INT
DECLARE @SampleType nvarchar(50)

SET @MaxDaysOld = 10
SET @SamplePercent = 25 --25
SET @SampleType = 'PERCENT' --'ROWS'

BEGIN TRY
DROP TABLE #OldStats
END TRY
BEGIN CATCH SELECT 1 END CATCH

SELECT
RowNum = ROW_NUMBER() OVER (ORDER BY ISNULL(STATS_DATE(object_id, st.stats_id),1))
,TableName = OBJECT_SCHEMA_NAME(st.object_id) + '.' + OBJECT_NAME(st.object_id)
,StatName = st.name
,StatDate = ISNULL(STATS_DATE(object_id, st.stats_id),1)
INTO #OldStats
FROM sys.stats st WITH (nolock)
WHERE DATEDIFF(DAY, ISNULL(STATS_DATE(object_id, st.stats_id),1), GETDATE()) > @MaxDaysOld
ORDER BY ROW_NUMBER() OVER (ORDER BY ISNULL(STATS_DATE(object_id, st.stats_id),1))

DECLARE @MaxRecord INT
DECLARE @CurrentRecord INT
DECLARE @TableName nvarchar(255)
DECLARE @StatName nvarchar(255)
DECLARE @SQL nvarchar(MAX)
DECLARE @SampleSize nvarchar(100)

SET @MaxRecord = (SELECT MAX(RowNum) FROM #OldStats)
SET @CurrentRecord = 1
SET @SQL = ''
SET @SampleSize = ISNULL(' WITH SAMPLE ' + CAST(@SamplePercent AS nvarchar(20)) + ' ' + @SampleType,N'')

WHILE @CurrentRecord <= @MaxRecord
BEGIN

SELECT
@TableName = os.TableName
,@StatName = os.StatName
FROM #OldStats os
WHERE RowNum = @CurrentRecord

SET @SQL = N'UPDATE STATISTICS ' + @TableName + ' [' + @StatName + ']' + @SampleSize + ';'

PRINT @SQL

EXEC sp_executesql @SQL

SET @CurrentRecord = @CurrentRecord + 1

END

-- Clears the procedure cache for the entire server
DBCC FREEPROCCACHE;

-- Update all usage in the database
DBCC UPDATEUSAGE (0);

Useful SQL Server Statistics Queries

-- Check Stats for specific tables

SELECT object_name(sp.object_id) AS [Table]
      ,sp.stats_id
         ,name
         ,filter_definition
         ,last_updated
         ,rows
         ,rows_sampled
         ,steps
         ,unfiltered_rows
         ,modification_counter
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = object_id('SPECIFY TABLE HERE');

-- Check stats for all user tables on current database

SELECT SCHEMA_NAME(schema_id) AS [SchemaName]
      ,obj.name AS [TableName]
      ,obj.object_id
         ,stat.name [StatName]
         ,stat.stats_id
         ,filter_definition
         ,rows
         ,rows_sampled
         ,steps
         ,unfiltered_rows
         ,last_updated
         ,modification_counter
FROM sys.tables AS obj
JOIN sys.stats stat ON stat.object_id = obj.object_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE obj.type <> 'S'
ORDER BY last_updated ASC;

--Check Stats for current database where the leading column has been modified over 1000 times

SELECT SCHEMA_NAME(schema_id) AS [SchemaName]
      ,obj.name AS [TableName]
      ,obj.object_id
         ,stat.name [StatName]
         ,stat.stats_id
         ,last_updated
         ,modification_counter
FROM sys.tables AS obj
JOIN sys.stats stat ON stat.object_id = obj.object_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE modification_counter > 1000
AND obj.type <> 'S';

Tuesday, 15 October 2013

Finding SYSADMINS on your SQL Server

SELECT [name]
                  ,CASE WHEN (IS_SRVROLEMEMBER('sysadmin', [name])) = 1
THEN 'YES' ELSE 'NO' END AS [is_sysadmin]
                  ,CASE WHEN (IS_SRVROLEMEMBER('securityadmin', [name])) = 1
THEN 'YES' ELSE 'NO' END AS [is_securityadmin]     
                  ,[type_desc]
                  ,[is_disabled]
                  ,[default_database_name]
FROM [master].[sys].[server_principals]
WHERE [type] NOT IN ('R','C')
AND [is_disabled] = 0
ORDER BY [is_sysadmin] DESC

;

Tuesday, 17 September 2013

Add Primary Key To All Tables Using The Index Keys From Clustered Unique Indexes

I needed to add a Primary Key to each of my tables within my Coda Financials Database. This was required so I could institute replication...

Fortunately each of the tables I required for replication have Unique Clustered Indexes on them. This obviously meant I could add a Primary Key to each of these tables using the Index Key Columns from these Unique Clustered Indexes.

I wanted to go down this route because the application checksums all rows in each table, so if I added an extra Primary Key column it would mean having to run Coda's FixCheck application over all the tables. This way I don't need to do this.

I came up with the below script to save me writing each individual ALTER Table statement:

DECLARE @IndexInfoTemp TABLE
(
index_name VARCHAR(250)
,index_description VARCHAR(250)
,index_keys VARCHAR(250)
)
DECLARE @IndexInfo TABLE
(
table_name SYSNAME
,index_name VARCHAR(250)
,index_description VARCHAR(250)
,index_keys VARCHAR(250)
)
DECLARE @Tables TABLE
(
rowid INT NOT NULL IDENTITY(1, 1)
,tablename SYSNAME
)
DECLARE @MaxRow INT
DECLARE @CurrentRow INT
DECLARE @CurrentTable SYSNAME
INSERT INTO @Tables
SELECT DISTINCT t.name
FROM sys.indexes i
INNER JOIN sys.tables t
ON i.object_id = t.object_id
WHERE i.name IS NOT NULL
SELECT @MaxRow = @@ROWCOUNT
,@CurrentRow = 1
WHILE @CurrentRow <= @MaxRow
BEGIN
SELECT @CurrentTable = tablename
FROM @Tables
WHERE rowid = @CurrentRow
INSERT INTO @IndexInfoTemp
EXEC Sp_helpindex
@CurrentTable
INSERT INTO @IndexInfo
(table_name
,index_name
,index_description
,index_keys)
SELECT @CurrentTable
,index_name
,index_description
,index_keys
FROM @IndexInfoTemp
DELETE FROM @IndexInfoTemp
SET @CurrentRow=@CurrentRow + 1
END --WHILE
SELECT table_name
,index_name
,index_description
,index_keys
,'ALTER TABLE ' + table_name
+ ' ADD CONSTRAINT pk_' + table_name
+ ' PRIMARY KEY(' + index_keys + ');' AS [Create Primary Key]
FROM @IndexInfo
WHERE index_description LIKE 'clustered, unique%'
AND table_name <> 'bi_params_transitionloading'
ORDER BY table_name;

Thursday, 11 April 2013

Processor Queue Length on SQL2005 and above...

The processor queue length is becoming less important as a SQL Server performance metric since SQL 2005.

In SQL 2005 and above the SQL server does not queue its work to the Windows Op System, it now has a user mode scheduler which is where it queues tasks that are waiting.

The best way to view the number of tasks queued in SQL Server is to use the DMV sys.dm_os_schedulers:

SELECT AVG([runnable_tasks_count])
FROM [sys].[dm_os_schedulers]
WHERE [status] = 'VISIBLE ONLINE';

This should return zero, CPU pressure would show if this is NOT zero consistently.


Wednesday, 20 March 2013

Last Running Query Based on SPID

Got this from Pinal Dave's Blog. Very useful Blog:

http://blog.sqlauthority.com

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = [SPID ID you wish to query]
SELECT TEXT, USER
FROM
sys.dm_exec_sql_text(@sqltext);


In the future sys.sysprocesses is going to depreciated by Microsoft in favour of the System DMV's. 

Unfortunately unlike sys.sysprocesses, sys.dm_exec_requests only holds sql handles for active sessions. Microsoft have advised that deprecation of sys.sysprocesses will not happen until ALL functionality can be replaced by the DMV's, and clearly there is still some way to go....

Once the sys.dm_exec_requests DMV is fixed so it holds the handles for all sessions I would say that the above could be changed to:

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.dm_exec_requests
WHERE session_id =
[SPID ID you wish to query]
SELECT TEXT, USER
FROM
sys.dm_exec_sql_text(@sqltext);




Thursday, 21 February 2013

Useful Stored Procedures for Linked Servers

sp_tables_ex
Parameters
  • @table_server
  • @table_name
  • @table_schema
  • @table_catalog
  • @table_type
sp_columns_ex
Parameters
  • @table_server
  • @table_name
  • @table_schema
  • @table_catalogue
  • @column_name
sp_help is also a very useful system stored procedure
Parameters
  •  @objname

Tuesday, 16 October 2012

SQL Server Uptime, SQL 2008 onwards



SELECT DATEDIFF(day,[sqlserver_start_time],GETDATE()) AS [Days SQL Server Has Been Up]
FROM sys.dm_os_sys_info;

Monday, 8 October 2012

Moving User Database Files



Use the following Script to check the location of your database files, and the logical name of the file:

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('Database Name Here');
GO

1.) Run the following Transact SQL to change location of your database files

ALTER DATABASE model MODIFY FILE ( NAME = logical name here , FILENAME = 'Type_New_Location_Here\physical database file name here.mdf' );

ALTER DATABASE model MODIFY FILE ( NAME = logical name here , FILENAME = 'Type_New_Location_Here\physical database file name here.ldf' );

2.) Take Database Offline

ALTER DATABASE [Database_Name] SET OFFLINE;

3.) Copy Physical Files to new location
4.) Set Database back online.

ALTER DATABASE [Database_Name] SET ONLINE;

Check new file location for Databases:


SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('Database Name Here');
GO

5.) Once you are completely happy that the files are in the correct location, delete them from the old location.