Video:SQL Server Performance Tuning with Wait Statistics (PageLatch - PageIOLatch - Latch)

3rd video of our Waits and Queues series. Latch waits are common and could be an indication of serious performance issues, but understanding these waits was never  that simple...

SQL Server Performance Tuning with Wait Statistics (PageLatch - PageIOLatch - Latch) from aasim abdullah on Vimeo.

Video: SQL Server Performance Tuning with Wait Statistics (CXPACKET)

CXPACKET is a common wait type, resulted by parallel query execution. But does occurrence of CXPACKET wait is always a sign of performance problem???

A lot more written. But here is our second animated video to learn it in a very simple way.

SQL Server Performance Tuning with Wait Statistics (CXPACKET) from aasim abdullah on Vimeo.

Video: SQL Server Performance Tuning with Wait Statistics (Introduction)

To investigate performance bottleneck, Wait Statistics is a power full tool which a DBA like to use on priority. Understanding waits is not that difficult but the way books and blog entries describe, makes it more complicated.

This is first out of 5 videos series. A simple way to understand internals of WAITS and how they can effectively be used to resolve SQL Server performance problems.

SQL Server Performance Tuning with Wait Statistics (Introduction) from aasim abdullah on Vimeo.

SQL Server : Extracting Data From VSS to SQL Server

Recently, when I was looking to generate some reports for sql files we have in Visual SourceSafe (VSS), I have decided to extract data from VSS to SQL Server, where I can play with data, more easily. 
One tool (off course free) I found which can help for this extraction. This tool  V2M is free to download but works better with less files/no of labels on VSS.
In my case it was not that helpfull and I have to use built in reports from VSS to extract data to text and then to SQL Server, as following.

1-On VSS, open your project, move to your desired folder, right click on it, and then click "Show History"

2- On Project History Options, check on "Recursive" and "Include file Histories" to get versions history for each file and hit OK button

3- It will take time to generate history. Once history page is open hit "Report" button and select "File" as report destination and press OK. It will ask location for file, provide and hit save button.

4- Generated report would be in text format and obviously NOT so simple to export to SQL Server, but not that tricky. You can copy paste whole text to Excel to format it manually and it would take more less time.

SQL Server : Script to Find and Create Missing Tables for Database Sychronization

Different methods and tools are available to synchronize two SQL Server databases. But one simple way to synchronize is using TSQL Script to find difference and create missing structures. Following script is for the same purpose. Script can be used to find and create missing tables on target database by comparing tables structure with source database.

SQL Server: Row Count for Specific Data Page

Recently found that for one of our production server, we have to use repair_allow_data_loss parameter to resolve page corruption. But question was how many rows we can loss for a specific corrupt page ???

Rows count on a specific page can be found if we get a page header print  by DBCC Page(). Obviously, before using DBCC Page() you must enable trace (3604).

DBCC TRACEON(3604)

GO

DBCC PAGE('DatabaseName',1,323008,0)

GO

In above case DatabaseName is my database name, 1 is data file id, 323008 is page number for which we need number of rows and 0 parameter value is for print header only. According to MSDN, we have 4 options for this parameter.

• 0 - print just the page header
• 1 - page header plus per-row hex dumps and a dump of the page slot array (unless its a page that doesn't have one, like allocation bitmaps)
• 2 - page header plus whole page hex dump
• 3 - page header plus detailed per-row interpretation

In output, under Page Header section, m_slotCnt shows number of rows on this particular page. In my case it is 736.

SQL Server: Client IP Along with DDL Change Log Using Service Broker

Last day, we have discussed all three possible methods for DDL Change Log and as per my suggestions, if you don’t need to conditionally allow/disallow changes then Service Broker is the best way to capture these changes. This method additionally allows you to submit change information to a separate instance on internet as a loosely coupled message.

One of blog reader raised a question that what else we need to add in script if we also need to capture machine IP from where change is coming.

Well answer is simple. We already have information of SPID so we can use this SPID and get client machine IP address by querying sys.dm_exec_connections.  

Change already defined stored procedure as following.

SQL Server: Three Common DDL Change Log Methods

Who is changing your objects (tables, views, stored procedures, functions etc) or creating new one, or who actually deleted one or more objects? These are normal questions when more than one person are working on a same database.

Production environment is mostly kept secure for unauthorized access and few known persons are allowed to make changes BUT still you need to keep a track of these changes and if it’s a development database then it is also must to keep a complete log of each change.

Three major ways, we can keep track of these changes.

1.                 DDL Trigger & Event Notifications

2.                 Extended Events

3.                 Service Broker & Event Notifications

DDL Trigger method is most commonly used method, where we write a ddl (after) trigger on each database separately and using information from event notifications, we decide whether to rollback any DDL change or just dump change information to a table.

How to create a DDL Trigger?

Extended Events, is the most advance method, not only for DDL change tracking but it’s going to be next biggest tool for DBAs. SQL Server 2012, introduced three new events for DDL change tracking.

1.                 object_altered

2.                 object_created

3.                 object_deleted

Paul Randal script for extended event creation is good one to follow, but don’t forget to change events.

 Service Broker (with event notifications), is the best way I have ever found for DDL Change Tracking before SQL Server 2012. Though its initial steps are bit lengthy, that is why; most people avoid using this method.

Using service broker, you can dump all databases changes data to a single table on an instance, or you can transmit changes information as a message to other instance on internet (if need to create a single point of administration for multiple instances).

(What is Service Broker and what type of objects you need to create, can be found here and here)

Use following simple steps to create DDL Changes Log, for multiple databases on an instance.

SQL Server: SET SET, A Strange Compatibility Upgradation Issue

During upgradation of a client database from SQL Server 2000 (Compatibility Level 80) to SQL Server 2005 (Compatibility Level 90), I have found that there are changes which need to apply for compatibility level 90, that are still not documented. Like everyone knows that *= or =* type joins are not acceptable in compatibility level 90. But what about following simple code.

USE [master]

GO

ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 80

GO

USE [AdventureWorks]

GO

DECLARE @MyVar INT

SET SET @MyVar = 5
Executing above code with compatibility  level 80 doesn’t generate any error but when we execute same code with COMPATIBILITY_LEVEL =90, it will generate following error.

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.

SQL Server: Script to Find Tables in Stored Procedures, That Are Without NonClustered Index

From my query bank here is another useful script which can help a Database Administrator to list down stored procedures with tables names which are used in stored procedures but don't contain and non clustered index.

Query Output:

SQL Server: Indexes List With Key and Involved Columns Name Alongwith Usage Statistics

A year back I have shared a script from query bank, which can be helpful to get indexes list of a database, with key and involved columns. Being a DBA, I never remember a day, without using this script. 

Getting detail of indexes on a database most of the time I also need indexes usage statistics, through which I can figure out which indexes are being used and which indexes can be discarded. 

To avoid to use two separate scripts, let me share following script which brings both, usage and structural information for all indexes of a database or a given table.

Script output

User Seek +  User Scans will decide which index are useful and which are just burden for database. Indexes with less seek + scan should be removed for better DML operations performance.

SQL Server: Placing Alert for Compatibility Level Change in SQL 2005

Microsoft SQL Server allows its users to keep behavior of a database compatible to its older versions. Like, if someone is using “*=” type of left outer joins in some quires/Stored Procedures as she created it for SQL Server 2000. Though such join are not allowed in SQL Server 2005 and subsequent versions but one still can keep database behavior as SQL Server 2000 by keeping its compatibility level to 80.

Recently, a client reported that someone (DBA or Application) is changing his database compatibility, which should remain compatible to SQL Server 2000 (compatibility level 80). He wants to know at what time this change is being made.

SQL Server 2008 and subsequent versions keep record of this compatibility change to its log, but SQL Server 2005 has no such facility. It means, in SQL Server 2005, you never know when someone has changed compatibility level.

In SQL Server 2008 and subsequent versions one can change compatibility level of a database by following simple TSql statement.

ALTER DATABASE AdventureWorks SET COMPATIBILITY_LEVEL = 90;

But in SQL Server 2005, only method to change this compatability level is its system stored procedure i.e. sys.sp_dbcmptlevel. SQL Profiler is the only place where you can trace when this stored proecdure was executed. But what if, we need to place an alert for this change and generate a mail for this change. Or what if, we need to stop users/applications to change a database compatability level.

Only way to achieve this functionality is,  to update system stored procedure  sp_dbcmptlevel.

Lets perform this task, step by step.

Step 1:  Stop SQL Server 2005 services

Step 2:  Login using DAC (Dadicated Administrative Connection). For this right click on SQL Server 2005 service, on Advanced tab, change startup parameters by adding -m; at existing values.

Step 3: Start SQL Server 2005 services

Step 4: Open SQL Server Management Studio and open Database Engine Query

Step 5: Login as valid sysadmin user or ADMIN:InstanceName

Step 6: Change mssqlsystemresource database to read_write mode

Step 7: It’s the time to update our system stored procedure i.e. sp_dbcmptlevel. If you need to keep only comptability level to 80 or 90 then change following lines of stored procedures with same values i.e.80 or 90 or as per your choice.

select  @cmptlvl60 = 60, 
@cmptlvl60 = 65,
@cmptlvl60 = 70,
@cmptlvl60 = 80,
@cmptlvl60 = 90, 

And if you also need to add a mail alert for this change then add following code in error control portion of stored procedure.

DECLARE @bodyText VARCHAR(200)

SET @bodyText='User '
+ CONVERT(VARCHAR,SYSTEM_USER)  
+' trying to change Compatibility Level of Database '                            + CONVERT(VARCHAR,@dbname)
+ ' at '
 + CAST(GETDATE() AS VARCHAR(50)) 
EXEC msdb.dbo.sp_send_dbmail @recipients='essmess@gmail.com;', --Change Email Address Accordingly 
@subject = 'Compatibility Level Change Alter', 
@profile_name = 'DBTeam', --Change DB mail Profile Accordingly 
@body = @bodyText, 
@body_format = 'TEXT' ;

Here is complete updated script of stored procedure. (This script is only applicable to SQL Server 2005, for SQL Server 2008 and subsequent version, its totally different, which you can get by sp_helptext)

Step 8: Change mssqlsystemresource database to read_only mode

Step 9: Close SSMS session, stop SQL Server services and change its startup parameters back to normal.

Step 10: Start SQL Server Services and you are done.

SQL Server: Why a Session With sp_readrequest Takes so Long to Execute

While applying, Long Running Sessions Detection Job on a production server, we start receiving alert that a session is taking more then 3 minutes. But what actually this session was doing. Here is the alert report.

SP ID	Stored Procedure Call	DB Name	Executing Since
58	msdb.dbo.sp_readrequest;1�	msdb	3 min

sp_readrequest is a system stored procedure, which basically reads a message request from the the queue and returns its  contents.

This process can remain active for a time we have configured for parameter DatabaseMailExeMinimumLifeTime, at the time of database mail profile configuration. 600 seconds is the default value for this external mail process. According to BOL DatabaseMailExeMinimumLifeTime is the The minimum amount of time, in seconds, that the external mail process remains active.

This can be changed, at the time of mail profile configuration or you can just use update query to change this time.

UPDATE msdb.dbo.sysmail_configuration

SET paramvalue = 60 --60 Seconds

WHERE paramname = 'DatabaseMailExeMinimumLifeTime'

We have changed this to 60 seconds to resolve our problem.

SQL Server: Template Explorer, A Developer’s Close Friend

How many of us really memorize all create, update or drop/delete scripts. Very honestly, I just remember Create Procedure and Create Function scripts. But, reality is that, we need not to remember all these codes/scripts, especially when Template Explorer is there for our help.

If you can’t see Template Explorer  in your SQL Server Management Studio, then just move your mouse pointer to top menu, view , Template Explorer or if you are short keys fan then just press Ctrl+Alt+T from your keyboard.

See how quickly I can create a new trigger.

 

SQL Server Errors: ORDER BY items must appear in the select list if SELECT DISTINCT is specified

SQL Server force you to put columns in SELECT DISTINCT list which are part of ORDER BY clause. But what if we don’t want to add that column/s in SELECT list. Lets try it.

--create temporary table to hold records

CREATE TABLE #DistinctSortTest (Val1 INT, Val2 INT)

GO

--insert some records

INSERT INTO #DistinctSortTest

VALUES (1,100),(8,55),(3,33),(1,1),(9,999)

GO

--lets see what we have in table

SELECT * FROM #DistinctSortTest

GO

 
From this table we need only “Val1” column with distinct values BUT sorting output with “Val2”. Lets try simple query.

SELECT DISTINCT Val1

FROM #DistinctSortTest

ORDER BY Val2

Opps. Error

Msg 145, Level 15, State 1, Line 1

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

To resolve this problem, we can use GROUP BY clause with MIN()or MAX() function in ORDER BY clause. 

SELECT  Val1

FROM #DistinctSortTest

GROUP BY Val1

ORDER BY MIN(Val2)

Through GROUP BY (all select columns) we will achive functionality of DISTINCT and MIN()/MAX() functions for sorting. MIN() in Order By clause can be used for ASC sort and MAX() for DESC sort.

--drop temporary table when not required

DROP TABLE #DistinctSortTest

SQL Server: Disable Logon Trigger Using DAC to Resolve Login Problem

Recently I have received a mail from one of blog reader, who explained his problem as following:

“I have tried scrip to create logon trigger from your blog post “Restrict Login from Valid Machine IPs Only (Using Logon Trigger)” BUT problem is that, I forgot to put localhost in my safe list, and now I am unable to login to my instance.”

Well, if same happened to you, then you need to login using Dedicated Administrator Connection. What is DAC and how to you use it Read This.
DAC can be established using sqlcmd or through SSMS. On command prompt, type this to establish connection.

Sqlcmd –S localhost –d master –A

You can provide instance name instead of localhost. Next thing is to disable our logon trigger, using following command.

DISABLE TRIGGER tr_LogOn_CheckIP ON ALL SERVER

Where “tr_LogOn_CheckIP” is the name of our logon trigger. On next line type GO to execute DISABLE command.

Now you can login to your database server. Once login, check out trigger is disabled.

You can achieve all this through SQL Server Management Studio. To establishing dedicated connection, click on  FILE----NEW----Database Engine Query

Login through valid SYSADMIN user, by providing server name with extra word and a colon, i.e. Admin:

In query window, type same tsql and execute to disable trigger.

And never forget to add your server IP or <localhost> in safe list, while creating logon trigger.

SQL Server: Keeping Log/Alert for Job Disable/Enable Status

To monitor production database servers, Database Administrators create different jobs and depends upon these jobs to work for them i.e. to check if server have enough space, database is not corrupt, queries are not running slow, index defragmentation and many more. BUT what if somehow, someone accidently disabled a job and forgot to enable it back. No alert will be created as job is disabled. Or it can be fatal when you need to restore a database and found that backup job was not working as it was disabled by someone ;)

Is there any way to get alert if someone changes any job status on production server?

YES, by creating following trigger on msdb.dbo.sysjobs can resolve this problem. It will detect any change in job status and will mail a message like following to your DBA team.

Job "Daily Full Backup" is recently DISABLED by user aasim.abdullah with session id 167 and host name IdeaWrox-DB01 at Sep 12 2012 4:00:03:673AM

 

 

SQL Server: Simple Way to Swap Columns values

To resolve a problem, sometime we start thinking at high level while, simple solutions of said problem are available. This is what happened to me, when one of my colleagues (Tehman) asked me how to swap two column values in a table.

My answer was, create a third column (temporary) and swap using this third column, which you can remove later on. Here was the plan.

1. Move Col2 data to Col3
2. Move Col1 data to Col2
3. Move Col3 data to Col1
4. Drop Col3

-- Create Temporary table to hold values

CREATE TABLE #ForSwappingTest ( Col1 VARCHAR(50), Col2 VARCHAR(50))
-- Insert test reocrds
INSERT INTO #ForSwappingTest (Col1,Col2)
VALUES ('A','X'),
('B','Y'),
('C','Z')
-- Check Results
SELECT * FROM #ForSwappingTest
-- Add third column to hold data temporarily
ALTER TABLE #ForSwappingTest ADD  Col3 VARCHAR(50)
-- Start Swaping
UPDATE #ForSwappingTest
SET COL3 = COL2

UPDATE #ForSwappingTest

SET COL2 = COL1

UPDATE #ForSwappingTest

SET COL1 = COL3
-- Remove additional temporary column
ALTER TABLE #ForSwappingTest DROP COLUMN Col3
--Drop temporary table when not required
DROP TABLE #ForSwappingTest

But he came with a very simple solution, by writing following simple query.

UPDATE #ForSwappingTest
SET Col2 = Col1,
Col1 = Col2