Home » 2016 » December

Monthly Archives: December 2016

What is “SET STATISTICS TIME”?

When you turn on this option, it will display the number of milliseconds required to parse, compile, and execute each statement. By default, this option is disabled.

SET STATISTICS TIME ON;
GO
SELECT * FROM [HUMANRESOURCES].[EMPLOYEE]

Output: -
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 1 ms.

(290 row(s) affected)

SQL Server Execution Times:
 CPU time = 0 ms, elapsed time = 60 ms.

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

How to use DTA (Database Tuning Advisor) with Plan Cache option?

SQL Server 2012 comes up with new workload option in DTA (Database Tuning Advisor). In addition, on existing options File or Table, Microsoft gives option to fetch workload from plan cache of SQL Server. In this case, the DTA will select the top 1,000 events from the plan cache based on total elapsed time of the query (that is, based on the total_elapsed_time column of the sys.dm_exec_query_stats DMV). This can be increased using –n option of Database Tuning Advisor command line utility.

dta -E -D DatabaseName -ip –n 2000-s SessionName1

Let’s try it: –

Step 1: Execute query and ensure Cache has plan for required database

–Clear Plan Cache

DBCC FREEPROCCACHE

GO

–Execute fresh statement

SELECT * FROM [HUMANRESOURCES].[EMPLOYEE] WHERE BUSINESSENTITYID > 10

GO

–Check Plan Cache has plan for ADVENTUREWORKS2012 database

SELECT * FROM SYS.DM_EXEC_CACHED_PLANS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE)

WHERE DBID=DB_ID(‘ADVENTUREWORKS2012’)

Step 2: Open up DTA and under the “General” tab select

Workload: Plan Cache

Database for work load analysis: AdventureWorks2012

Database and tables to tune: relevant database and table you want to tune. In my case I selected all tables under AdventureWorks2012 database.

Step 3 – Leave other options as default. You can change them as per your requirements. Click on “Start Analysis” and it gives you recommendations as shown below.

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

SQL Server 20082008 R22012 || Remove Node from failover cluster

Steps to remove node from failover cluster for SQL Server 2008 onwards :-

Note :-

a) Same process will be used to remove node from failover cluster

b) Same process will be used when you left with only one node, this will clear cluster group as well

c) Old process of uninstallation from Add and Remove program in Control Panel will not work

1) Open SQL Server installation Center > Go to Maintenance Option from Left > Click on “Remove Node from a SQL Server failover cluster” from right

2) Click on Run to start uninstallation process

3) Click “OK”. In case of any failure, you need to fix that before uninstallation

4) Click Next

5) Select Instance Name & click Next

6) Click Remove to start uninstallation

7) Uninstallation in progress

8) Uninstallation successfully completed.

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

MSDB log file grown large – SLEEP_MSDBSTARTUP waittype

Issue: MSDB database log file is getting full very rapidly and became very huge. Currently, T-Log file is of 5GB whereas Data file is only 1GB for MSDB on server.

Findings: We found system owned open transaction in MSDB which is causing issue.

Transaction information for database ‘msdb’.

Oldest active transaction:

SPID (server process ID): 19s

UID (user ID) : -1

Name : CTraceEvDataQueue

LSN : (12968:15110:1)

Start time : May 7 2016 12:33:14:507PM

SID : 0x0

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

When we try to find SPID 19 details from sys.sysprocesses, we found that session is causing SLEEP_MSDBSTARTUP waittype.

SLEEP_MSDBSTARTUP waittype Occurs when SQL Trace waits for the msdb database to complete startup. This is very rare watitype which we come across in our day to day production support.

Resolution: As we all know, we cannot kill SYSTEM session IDs, We need to restart SQL Services to get rid out of this problematic session.

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

%d bloggers like this: