Monthly Archives: November 2017

SQL 2016 statistics issue

After I upgrade SQL Server 2008R2 to 2016 with compatibility level 130 we experience statistics issues. With the database option LEGACY_CARDINALITY_ESTIMATION setting ON the database was performing good. So this was the quick fix.

I decided to start with a fresh set of statistics so this is what I did. 

  • I turned of the LEGACY_CARDINALITY_ESTIMATION
  • drop all auto created statitics on that particular database.
  • clears all executions plans for that specific  database.
  • did some index tuning.

Lets put this together in a SQL script.

-- Step 1 - Turn off the LEGACY_CARDINALITY_ESTIMATION option
use <database_name, sysname, your_database_name>
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = Off;
GO

-- Step 2 - Generate DROP STATISTICS statements for all auto created stats.
SELECT 
 'table_name' = d.name
,'DROP STATISTICS ' + quotename(Schema_NAME(d.Schema_id)) + '.' + 
quotename(OBJECT_NAME(a.object_id)) + '.' + 
quotename(a.name) as colum_name
FROM sys.stats a
INNER JOIN sys.Objects d ON d.Object_id = a.object_id
WHERE auto_created = 1 AND User_Created = 0 and d.is_ms_shipped = 0
GO

-- Step 3 - Clears all executionsplans for that specific database
USE <database_name, sysname, your_database_name>
GO
DECLARE @DBID INT = (SELECT DB_ID())
DBCC FLUSHPROCINDB(@dbid);
GO

SQL Server Generate 100% CPU query

In order to test my SQL Server Resource Governor setup I needed a query that generates 100 CPU% load. This query will do the trick.
Note: U will need admin privileges for this script.

SELECT MyInt = CONVERT(BIGINT, o1.object_id) + CONVERT(BIGINT, o2.object_id) + CONVERT(BIGINT, o3.object_id)
INTO #temp
FROM sys.objects o1
JOIN sys.objects o2 ON o1.object_id < o2.object_id
JOIN sys.objects o3 ON o1.object_id < o3.object_id

SELECT SUM(CONVERT(BIGINT, o1.MyInt) + CONVERT(BIGINT, o2.MyInt))
FROM #temp o1
JOIN #temp o2 ON o1.MyInt < o2.MyInt

You can check how SQL Server loads the CPU of your server using the following T-SQL script:

DECLARE @CPU_BUSY int, @IDLE int
SELECT @CPU_BUSY = @@CPU_BUSY, @IDLE = @@IDLE 
WAITFOR DELAY '00:00:00:500'
SELECT(@@CPU_BUSY - @CPU_BUSY) / ((@@IDLE - @IDLE + @@CPU_BUSY - @CPU_BUSY) * 1.00) * 100 AS CPUBUSY