Category: SQL

SQL 2008 : “Invoke or BeginInvoke” SP1 Error

I recently came across an error when installing SP1 on a Windows 2008 R2 x64/SQL 2008 Standard x64 environment which prevened me from installing the service pack:

“Invoke or BeginInvoke cannot be called on a control until the window handle has been created.”

As a work around I identified that I was able to right-click the update and select ‘Run as Administrator.’ I was then able to complete the update without issue.

SQL : Find Current Database Name

The following T-SQL will identify the name of the current Database and set the variable @dbname to the name of the database.

/* Establish Current Database Context */
declare @dbname varchar (200)
set @dbname = (SELECT DB_NAME())

This is useful when checking the existence of a table within the current database, for example:

IF object_id(@dbname +N’..tblFragStats’) IS NULL
    BEGIN
        CREATE TABLE tblFragStats (
           Date DATETIME, ObjectName CHAR (255), ObjectId INT,
           IndexName CHAR (255), IndexId INT,
           Lvl INT, CountPages INT,
           CountRows INT, MinRecSize INT,
           MaxRecSize INT, AvgRecSize INT,
           ForRecCount INT, Extents INT,
           ExtentSwitches INT, AvgFreeBytes INT,
           AvgPageDensity INT, ScanDensity DECIMAL,
           BestCount INT, ActualCount INT,
           LogicalFrag DECIMAL, ExtentFrag DECIMAL
            )
    END

This code will function on any version of Microsoft SQL, from 2000 SP4 onwards.

 SQL : Move TempDB

The following transact-SQL will move the tempdb data and log files to a different physical loation. Simply modify the FILENAME paths to suit, adding additional files where appropriate for your environment.

USE master
GO
ALTER DATABASE tempdb
    MODIFY FILE (NAME = tempdev, FILENAME = ‘N:\Data\tempdb.mdf’)
GO
ALTER DATABASE tempdb
    MODIFY FILE (NAME = templog, FILENAME = ‘N:\Data\tempdb.ldf’)
GO

After completing this you will need to take the SQL instance offline and bring it back onlin.

SQL : AUTO STATS Troubleshooting

Statistics for Query Optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed view. The query optimizer uses these statistics to estimate the cardinality, or number of rows, in the query result. These cardinality estimates enable the query optimizer to create a high-quality query plan.

To view the indexes and their last update time use the following command:

 SELECT
        o.name AS Table_Name
       ,i.name AS Index_Name
       ,STATS_DATE(o.id,i.indid) AS Date_Updated

FROM
        sysobjects o JOIN
        sysindexes i ON i.id = o.id

WHERE
        xtype = ‘U’ AND
        i.name IS NOT NULL

ORDER BY
        o.name ASC
       ,i.name ASC

To update statistic:

•     On a single index: UPDATE STATISTICS [Table_Name][INDEX_NAME]
•     On a single table: UPDATE STATISTICS[TABLE_NAME]
•     On the entire Database: EXEC sp_updatestats

 

 

SQL : Common SQL Wait Types

Using the command DBCC SQLPERF(WAITSTATS) you can get real time totals of all wait types within an SQL instance. The table below outlines some common wait types and what to look for when troubleshooting them. 

NETWORKIO	Waiting on network I/O completion. Waiting to read or write to a client on the network. Check NIC saturation.
LCK_x	Check for memory pressure, which causes more physical I/O, thus prolonging the duration of transactions and locks. View the following counter: Lock Wait Time (ms)
CXPACKET	On OLTP (On-line Transaction Processing) systems, if CXPACKET wait-types account for more than 5% of total locks SQL CPU parallelism can cause performance degradation. On Data WH systems if CXPACKET waits are 10% or greater then it is likely that parallelism is the cause of the performance issues.   Look to reduce parallelism or even disable it on OLTP systems. OLTP systems best practice is to disable it completely.
I/O_COMPLETION	Indicates an I/O bottleneck. Identify disk bottlenecks, using IoStallMS values and Perfmon counters: PhysicalDisk: Disk Sec/read, Disk Sec/write, Disk Queues SQL Server Buffer Manager: Page Life Expectancy, Checkpoint Pages/sec, Lazy Writes/sec ·         SQL Server Access Methods for correct indexing: Full Scans/sec, Index Searches/sec See the Memory: Page Faults/sec   Identify IoStallMS values using the following query: /* Find DB ID */ SELECT DB_ID(‘SQLTraceDB’) as [Database ID] /* Display IoStallMS */ SELECT * FROM ::fn_virtualfilestats(7,-1)
EXCHANGE	Check for parallelism using sp_configure ‘max degree of parallelism’. Disable parallelism by setting 1 or change to # of CPU’s /2.
ASYNC_I/O_COMPLETION	Waiting for asynchronous I/O requests to complete. Identify disk bottlenecks, using IoStallMS values and Perfmon Counetrs: PhysicalDisk: Disk Sec/read, Disk Sec/write, Disk Queues SQL Server Buffer Manager: Page Life Expectancy, Checkpoint Pages/sec, Lazy Writes/sec ·         SQL Server Access Methods for correct indexing: Full Scans/sec, Index Searches/sec See the Memory: Page Faults/sec   Identify IoStallMS values using the following query: /* Find DB ID */ SELECT DB_ID(‘SQLTraceDB’) as [Database ID] /* Display IoStallMS */ SELECT * FROM ::fn_virtualfilestats(7,-1)   Full Scans/Sec This counter should always be captured. It shows how often a table index is not being used and results in sequential I/O. This is defined as the number of unrestricted full scans. These can be either base table or full index scans. Missing or incorrect indexes can result in reduced performance because of too high disk access.   Page Life Expectancy According to Microsoft, 300 seconds is the minimum target for page life expectancy. If the buffer pool flushes your pages in less than 300 seconds, you probably have a memory problem.
WAITFOR	Check common SQL Stored Procedures for WAIT FOR DELAY statements. This is a fixed delay within a stored procedure.
PAGELATCH_x	Usually indicates cache contention issues
PAGEIOLATCH_x	Usually indicates I/O issues. Check disk subsystem.
OLEDB	See Disk secs/Read and Disk secs/Write. If Disk secs/Read is high, add additional I/O bandwidth, balance I/O across other drives.   To get the Transact-SQL statement involved in OLEDB waits: DECLARE @Handle binary(20) SELECT @Handle = sql_handle FROM sysprocesses WHERE waittype = 0x0042 SELECT * FROM ::fn_get_sql(@Handle)

 

SQL Troubleshooting Wait Time

The following SQL script will export the output of ‘DBCC SQLPERF(WAITSTATS)’ into a temporary table named #waitstats.

CREATE TABLE #waitstats
(
    [Wait Type]        nvarchar(32) not null,
    [Requests]         float not null,
    [Wait Time]        float not null,
    [Signal Wait Time] float not null
)

INSERT INTO #waitstats EXEC(‘dbcc sqlperf(waitstats)’)

SELECT * FROM #waitstats
ORDER BY ‘Wait Time’ DESC

To delete the temporary table use the command:

DROP TABLE #waitstats 

The following SQL script will identify fragmented indexes and automaticallyrebuild them. Note that this should not be run duringbusy periods/production hours and should only be run against databases which are not partitioned:

{code lang:sql showtitle:false lines:false hidden:false}declare @tablename varchar (128)
declare @id int
declare @Cnt smallint
declare @nrc_mask int
declare tables cursor for
select TABLE_SCHEMA + ‘.’ + TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = ‘BASE TABLE’

create table #spt_space
( aidi int,
[rows] int null,
reserved dec(15) null,
AUTOSTATS char(2) null,
[Last Updated] datetime
)
open tables
/*
** Set NORECOMPUTE mask
*/
set @nrc_mask = 16777216

fetch next
from tables
into @tablename
while @@fetch_status = 0
begin

select @id = object_id(@tablename)

insert into #spt_space (aidi, reserved)
select @id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
update #spt_space set [rows] = i.[rows]
from #spt_space inner join sysindexes i
on #spt_space.aidi = i.id
where i.indid =indid and i.id = @id

update #spt_space set [AUTOSTATS] =
case (si.status & @nrc_mask)
when @nrc_mask then ‘OFF’
else ‘ON’
end,
[Last Updated] = stats_date(#spt_space.aidi, si.indid)
from sysindexes si inner join #spt_space
on #spt_space.aidi = si.id
where si.id = @id AND — Table
si.indid BETWEEN 1 AND 254 — Skip HEAP/TEXT index
fetch next
from tables
into @tablename
end

— Close and deallocate the cursor
close tables
deallocate tables

CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)

CREATE TABLE #bigtables (i int NOT NULL IDENTITY(1,1), tablename varchar(128))
INSERT #bigtables(tablename)
SELECT object_name(aidi) as tablename
FROM #spt_space
–WHERE rows >= 100000

SELECT * FROM #spt_space
DROP TABLE #spt_space
SELECT @Cnt = count(*) FROM #bigtables

DECLARE @DTime datetime
Set @DTime = GETDATE()

SET @id = 1
WHILE @id <= @Cnt
BEGIN
SELECT @tablename = tablename FROM #bigtables WHERE i = @id
INSERT INTO #fraglist
EXEC (‘DBCC SHOWCONTIG (”’ + @tablename + ”’)
WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS, FAST’)
SET @id = @id + 1
END

SELECT ObjectName, IndexName, CountPages, ExtentSwitches, BestCount, ActualCount, LogicalFrag FROM #fraglist ORDER BY LogicalFrag DESC
DROP Table #bigtables
DROP table #fraglist{/code}

Use the following code to rebuild indexes that are 10% or more fragmented (you can change this threshold by modifying the figure highlighted in RED):

{code lang:sql title:”SQL Query – Fix Fragmentation” lines:false hidden:false}declare @tablename varchar (128) 

declare @id int 
declare @Cnt smallint 
declare @nrc_mask int 
declare tables cursor for 
select TABLE_SCHEMA + ‘.’ + TABLE_NAME 
from INFORMATION_SCHEMA.TABLES 
where TABLE_TYPE = ‘BASE TABLE’ 

create table #spt_space 
( aidi int, 
[rows] int null, 
reserved dec(15) null, 
AUTOSTATS char(2) null, 
[Last Updated] datetime 
) 
open tables 
/* 
** Set NORECOMPUTE mask 
*/ 
set @nrc_mask = 16777216 

fetch next 
from tables 
into @tablename 
while @@fetch_status = 0 
begin 

select @id = object_id(@tablename) 

insert into #spt_space (aidi, reserved) 
select @id, sum(reserved) 
from sysindexes 
where indid in (0, 1, 255) 
and id = @id 
update #spt_space set [rows] = i.[rows] 
from #spt_space inner join sysindexes i 
on #spt_space.aidi = i.id 
where i.indid =indid and i.id = @id 

update #spt_space set [AUTOSTATS] = 
case (si.status & @nrc_mask) 
when @nrc_mask then ‘OFF’ 
else ‘ON’ 
end, 
[Last Updated] = stats_date(#spt_space.aidi, si.indid) 
from sysindexes si inner join #spt_space 
on #spt_space.aidi = si.id 
where si.id = @id AND — Table 
si.indid BETWEEN 1 AND 254 — Skip HEAP/TEXT index 
fetch next 
from tables 
into @tablename 
end 

— Close and deallocate the cursor 
close tables 
deallocate tables 

CREATE TABLE #fraglist ( 
ObjectName CHAR (255), 
ObjectId INT, 
IndexName CHAR (255), 
IndexId INT, 
Lvl INT, 
CountPages INT, 
CountRows INT, 
MinRecSize INT, 
MaxRecSize INT, 
AvgRecSize INT, 
ForRecCount INT, 
Extents INT, 
ExtentSwitches INT, 
AvgFreeBytes INT, 
AvgPageDensity INT, 
ScanDensity DECIMAL, 
BestCount INT, 
ActualCount INT, 
LogicalFrag DECIMAL, 
ExtentFrag DECIMAL) 

CREATE TABLE #bigtables (i int NOT NULL IDENTITY(1,1), tablename varchar(128)) 
INSERT #bigtables(tablename) 
SELECT object_name(aidi) as tablename 
FROM #spt_space 
–WHERE rows >= 100000 

SELECT * FROM #spt_space 
DROP TABLE #spt_space 
SELECT @Cnt = count(*) FROM #bigtables 

DECLARE @DTime datetime 
Set @DTime = GETDATE() 

SET @id = 1 
WHILE @id <= @Cnt 
BEGIN 
SELECT @tablename = tablename FROM #bigtables WHERE i = @id 
INSERT INTO #fraglist 
EXEC (‘DBCC SHOWCONTIG (”’ + @tablename + ”’) 
WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS, FAST’) 
SET @id = @id + 1 
END 

SELECT ObjectName, IndexName, CountPages, ExtentSwitches, BestCount, ActualCount, LogicalFrag FROM #fraglist ORDER BY LogicalFrag DESC 

DROP Table #bigtables 

/* ********************************* */
/* Perform the REINDEX – NOTE This will lock the Table in which the index resides */
/* ********************************* */

DECLARE @objectid int, @indexname varchar(200), @frag decimal, @SQL varchar(500)
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexName, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= 10
AND INDEXPROPERTY (ObjectId, IndexName, ‘IndexDepth’) > 0;
— Open the cursor.
OPEN indexes;
— Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexname, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN;
PRINT ‘Executing DBCC DBREINDEX (‘ +RTRIM(@tablename) + ‘,”’ + RTRIM(@indexname)
+ ”’) – LogicalFrag Currently: ‘ + RTRIM(CONVERT(VARCHAR(15),@frag)) + ‘%’;

SELECT @SQL = ‘DBCC DBREINDEX (‘ +RTRIM(@tablename) + ‘,”’ + RTRIM(@indexname) + ”’)’
EXEC (@SQL)

FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexname, @frag;
END;
— Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
— Delete the temporary table.
DROP TABLE #fraglist;
GO

EXEC sp_updatestats

{/code} 

SQL Transaction Log File Full Resolution

In the event you recieve the following notification on a SQL server:

Error: 9002, Severity: 17, State: 6
The log file for database ‘DB_Name’ is full. Back up the transaction log for the database to free up some log space. 

The following SQL code will dump the transaction log and shrink the transaction log file:

sp_helpdb db_name

       USE db_name

GO

BACKUP LOG db_name WITH TRUNCATE_ONLY

DBCC SHRINKFILE (db_name_log, TRUNCATEONLY)

Use the command below to set a simple recovery mode if desired – this will stop this happening again but will stop you being able to perform a point in time recovery:

ALTER DATABASE db_name SETRECOVERY SIMPLE

Once this process has been performed you should ensure a full backup is taken of the database.