Microsoft Innovation Center: Presentation Dec 18 2013

On December 18th, 2013, I have presented on topic "*SQL Server: Query Performance Tuning" at Microsoft Innovation Center, Lahore (Pakistan). Primary agenda was to discuss all possible methods to detect costly queries and handling these queries to obtain optimum performance. 

SQL Server : Database Schema Sychronization Script for Non Clustered Indexes

Non clustered indexes are the usual schema objects which a DBA usually like to  synchronize on same structured client databases. Following script should be executed on source database, to find and create missing indexes on target database.

(Note: These synchronization scripts are just modified by me, BUT are property of SQL Server community. Thanks to Amna Asif and other basic writers of these scripts)

SQL Server : Database Schema Sychronization Script for Column Default Values

During database schema synchronization, only missing columns creation is not enough, we also need to find what default values constraints are missing at target database. Following script I use for this purpose.

(Note: These synchronization scripts are just modified by me, BUT are property of SQL Server community. Thanks to Amna Asif and other basic writers of these scripts)

SQL Server : Database Schema Sychronization Script for Foreign Keys

Following schema synchronization script checks and creates if some foreign key is missing at target database as compare to source database. 

(Note: These synchronization scripts are just modified by me, BUT are property of SQL Server community. Thanks to Amna Asif and other basic writers of these scripts)

SQL Server : Database Schema Sychronization Script for Primary Keys

Following schema synchronizations script compare two given (source/target) databases to create missing primary keys at target database.

(Note: These synchronization scripts are just modified by me, BUT are property of SQL Server community. Thanks to Amna Asif and other basic writers of these scripts)

SQL Server : Database Schema Sychronization Script for Columns

As per my commitment with my blog readers, here is one more script I use for missing columns creation during database schema synchronization process.

(Note: These synchronization scripts are just modified by me, BUT are property of SQL Server community. Thanks to Amna Asif and other basic writers of these scripts)

Microsoft Innovation Center: Presentation Nov 27 2013

On November 27th, 2013, I have presented on topic "*SQL Server: 2012 or HEKATON" at Microsoft Innovation Center, Lahore (Pakistan). Primary agenda was to discuss all major and minor new features, introduced in SQL Server 2012 and up coming SQL Server 2014 code name HEKATON. And should we go for SQL Server 2012 or wait for SQL Server 2014.

SQL Server Community: Microsoft Virtual Academy

Recently, I have joined Microsoft Virtual Academy and found it an amazing, free, trusted and up to date source of learning about Microsoft Cloud Technologies. Being a student of SQL Server, I was found 5 interesting tracks. You can learn some advance topics about SQL Server in a very efficient way. Not only you can learn, but you can assess your learning through self-assessments tests.

Everyone is eligible, and anyone can learn, totally free of charges.

Currently 5 SQL Server tracks with around 50 videos are available.

SQL Server : Tables Relationship Diagram Using TSQL Script

How do you create relationship diagram/report between tables of a given database ? Mostly people use Database Diagram for this purpose, but this can be achieved by TSQL script as following.

Download Script

 

-- Tables Relationship Script

-- Script By: Syed Muhammad Yasir for http://connectsql.blogspot.com
-- Updated August 1, 2012
 

SELECT  CASE WHEN a.parent_object_id IS NULL

THEN parent.name + '-1--*-' + child.name
ELSE parent.name + '-1--1-' + child.name
END AS TablesWithRelations
FROM    ( SELECT DISTINCT
parent_object_id, referenced_object_id
FROM      sys.foreign_keys ) fk
LEFT JOIN ( SELECT DISTINCT
fkindexes.parent_object_id,
fkindexes.referenced_object_id
FROM    ( SELECT    fk.parent_object_id,
fk.referenced_object_id,
ixcolumns.index_id, COUNT(*) cindexes
FROM      ( SELECT    object_id,
            parent_object_id,
            referenced_object_id
  FROM      ( SELECT    row_number() OVER ( PARTITION BY parent_object_id, referenced_object_id
ORDER BY object_id ) rid,
              object_id, parent_object_id, referenced_object_id
              FROM      sys.foreign_keys ) fk
  WHERE     rid = 1 ) fk
JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
JOIN sys.index_columns ixcolumns ON ixcolumns.object_id = fkc.parent_object_id
                                    AND ixcolumns.column_id = fkc.parent_column_id
JOIN sys.indexes ix ON ix.object_id = ixcolumns.object_id
                       AND ix.index_id = ixcolumns.index_id
WHERE     ix.is_unique = 1
GROUP BY  fk.parent_object_id,
fk.referenced_object_id,
ixcolumns.index_id ) fkindexes
JOIN ( SELECT   fk.parent_object_id,
    ixcolumns.index_id,
    COUNT(*) cindexestotal
FROM     ( SELECT DISTINCT
                parent_object_id
      FROM      sys.foreign_keys ) fk
    JOIN sys.index_columns ixcolumns ON ixcolumns.object_id = fk.parent_object_id
GROUP BY fk.parent_object_id,
    ixcolumns.index_id ) totalindexes ON totalindexes.parent_object_id = fkindexes.parent_object_id
                                         AND totalindexes.index_id = fkindexes.index_id

WHERE   cindexestotal - cindexes = 0 ) a ON a.parent_object_id = fk.parent_object_id

                        AND a.referenced_object_id = fk.referenced_object_id
JOIN sys.tables child ON fk.parent_object_id = child.object_id
JOIN sys.tables parent ON fk.referenced_object_id = parent.object_id
ORDER BY TablesWithRelations

SQL Server has encountered 1 occurrence(s) of cachestore flush

SQL Server Log report from one of our production server was continuously showing following messages.

Log Date	Process Info	Process Text
2012-07-23T20:00:08.880	spid17s	SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2012-07-23T20:00:08.880	spid17s	SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2012-07-23T20:00:07.190	spid17s	SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2012-07-23T06:00:04.640	spid16s	SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2012-07-23T06:00:04.640	spid16s	SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2012-07-23T06:00:04.580	spid16s	SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

It happens when you configure user database with Auto Close option.

SQL Server, close a user database automatically, when last session is closed and reactivated when a login request is received.  We must keep this option OFF for a better performance. Why so read this.

------------------------------------------------------------------------------------

Read More about SQL Server Log Errors/Messages 

• DeviceIoControl failed, error 21
• This instance of SQL Server has been using a process ID of xxxx since mm/dd/yyyy
• SQL Server has encountered occurrence(s) of I/O requests taking longer than 15 seconds to complete
• The time stamp counter of CPU on scheduler id 13 is not synchronized with other CPUs.
• I/O is frozen on database DatabaseName

SQL Server: What I Have Learned in May 2011

Dedicated to Most Energetic SQL Expert Gail Shaw

SQL Tips & Tricks:

SQL Server: Column Value with Leading Zeros

SQL Server: How to Remove Extra Spaces From String Value

SQL Server: How to Remove Special Characters

SQL Server: Conditional WHERE clause (Filter for a Filter)

Third Party Tools:

SQL Server: How SQL Diag Became Easy for Every DBA

SQL Server: Shortcuts for TSQL Code in SSMS

SQL Server: sp_WhoIsActive, a Must Tool for a DBA

SQL Server: Keeping Log of Each Query Executed Through SSMS

Performance Tuning:

SQL Server: Why to Avoid TRIM functions in WHERE and JOIN clauses

SQL Server: Short Code Doesn’t Mean Smart Code

SQL Server: Which Performs Better, IN or EXISTS

SQL Server Management Studio:

SQL Server: Query Template, a Time Saving Feature

Database Management Views & Functions:

SQL Server: Create/Drop Scripts for All Existing Foreign Keys

SQL Server: How to Get All Indexes List With Involved Columns Name

SQL Server: Better Intellisence for TSQL Code in SSMS

For a developer or DBA it’s common to start their day with “SELECT * FROM” and in a daily routine work we type same lines of script many times. If you are a lazy developer or DBA like me then sometime it feels boring to type same code again and again. Intellisence in SQL Server 2008, saved lot of time but still it lacks few features, which other third party tools were offering before SQL Server 2008. 
Through such tools like SQL PROMPT, we have ability to type shortcuts in editor which after pressing Enter or Tab turn into a predefined code block. Like I will just type * and it will convert it to “SELECT * FROM”.

If you don’t like to purchase these tools but still want to make your life easy then you need SSMS Tools by Mladen Prajdić, totally free and you can download from here. Beside other good tools it has an option of SQL Snippets. Although it already have a good list of shortcuts but still you can add of your choice.

It has shortcuts like:

                                    SSC        =       SELECT  COUNT(*) FROM

                                    SSF         =       SELECT * FROM

                                    UPD       =       UPDATE <>

 SET    <>

 FROM   <>

SQL Server: sp_WhoIsActive, a Must Tool for a DBA

A mail with subject “Low Database Response” or a call with words “What happened to the database?” is normal in a SQL Server DBA’s life. To cope with such bad situations normally a DBA keeps his drawer filled with necessary tools and scripts. But like me, most of DBAs first query to check “What is happening actually” is shortest query in SQL Server i.e. sp_who or sp_who2. Second returns almost same data to sp_who but with more detail information.

sp_who 

sp_who2 

Though both system stored procedures are helpful to collect basic information to find out problematic sessions of database but to reach your desired rows, you have to skim through large number of rows and you will find more then 95% of rows useless. Some time information provided by sp_who or sp_who2 is not enough to understand the real problem which leads you to query some other DMVs for such information.

Thanks to Adam Machanic who resolved this problem. His stored procedure WhoIsActive is a must tool for a DBAs kit. This single stored procedure collects almost all necessary information for a DBA, which will be helpful to understand the real problem of a database. The beauty of this stored procedure is that it collects information using almost 15 DMVs but still returns only necessary rows.

SQL Server: Keeping Log of Each Query Executed Through SSMS

During a normal working day, a DBA or Developer executes countless queries using SQL Server Management Studio. Some of these queries, which are thought important, are saved and roughly 80% of query windows are closed without pressing save button. But after few minutes, hours or even days, most DBAs and Developers like me want their quires back, which they have executed but can’t save.

SQL Server Management Studio has no such feature through which we can get our unsaved queries back. If you need to keep log of each query you have executed in SSMS then you must install free tool “SSMS Tools Pack 1.9” by Mladen Prajdić, which contains “Query Execution History” and much more. 

SQL Server: What I have Learned in April 2011

Dedicated to Unbeaten Hero of SQL Server Community Jacob Sebastian

TSQL Tips & Tricks

SQL Server: Float to Varchar Conversion

SQL Server: Working With Date but With Format of Your Own Choice

SQL Server: Sorting Data in Ascending Order but NULL at the End

SQL Server Basics: TO_DATE() Function in SQL Server

SQL Server: LEN() or DATALENGTH()

SQL Server: Two Misconceptions about CHAR Data type

SQL Server Management:

SQL Server: How to Manage Error Log File on Production Server

SQL Server: How to Read Trace File to Detect Database Detachment

SQL Server: Script to Create Replica of Existing Database User

SQL Server: NULL As Nothing vs NULL As String Value in SSMS

Performance Tuning:

SQL Server: How to Analyze Ad Hoc Queries Workload

SQL Server: High Logical Reads for Strange Worktable

SQL Server Community:

SQL Server: A Query Slow in Application but Fast in SSMS

SSIS: Opportunity to Learn SQL Server Integration

SQL Server: A Query Slow in Application but Fast in SSMS

If someone is somehow related to SQL Server, he/she has must faced this common question that “I have query/stored procedure which is slow in the Application, but Fast in SSMS?” Most of us blame parameter sniffing for this problem but there are other factors, which can create this situation.

I think most authenticated and cleanly written article in response to this commonly asked question is Slow in the Application, Fast in SSMS? Understanding Performance Mysteries, written by Erland Sommarskog (MVP).

SSIS: Opportunity to Learn SQL Server Integration

If you had never used SSIS before, and want to learn it with an easy and quick way, Andy Leonard, has started a new Stairway to SSIS series for you. With simple, step by step and easy to understand methods, supported with all necessary screen shots, Andy has done a very good job.

Can you believe that series already have got 10000 hits with in single month.

What is SSIS? Step 1 of the Stairway to Integration Services

SQL Server: What I Have Learned In March 2011

Dedicated to Grant Fritchey, a Leader of SQL Server community.

TSQ Tips & Tricks

• SQL Server: TSQL Script to Get Hard Drives Detail
• SQL Server: Querying Case Insensitive Data with Case Sensitive Filters
• SQL Server: Cascade Delete
• SQL Server: The Magical OVER clause
• SQL Server: How to Get Strings Which Contains Special Characters
• SQL Server: How to Load Files From Given Directory To a Table

Performance Tuning

• SQL Server: Small Tables’ Clustered Indexes Fragmentation
• SQL Server: How to Analyze Blocking and Deadlocking

SQL Server Basic Concepts

• SQL Server: Understanding Output of SET STATISTICS TIME ON
• SQL Server: Execution Plan Major Points in Query Optimization
• SQL Server: Difference between Locking, Blocking and Dead Locking
• SQL Server: What is an Algebrizer ?

SQL Server Community

• SQL Server: Great Opportunity to Learn Indexes
• SQL Server: A Brief Introduction to SQL Server Denali
• SQL Quiz: Solve Simple TSQL Quiz and Win an Apple iPad

SQL Server Hardware Health

• SQL Server: Is Your Database Server's Hard Drive Working Properly?

SQL Tools

• SQL Server: How to Find Costliest Nodes in Graphical Execution Plan
• SQL Server: Multiple Objects Script but Save Each to Separate Files

SQL Server Management

• SQL Server: How to Avoid Big Single Error Log File on Production Servers

SQL Server Community: Great Opportunity to Learn Indexes

Indexes are fundamental object and have high importance. That is why almost every forth or fifth post of SQL Server discuss different aspects of indexes. Recently David Durant  started a remarkable series with caption “Stairway to SQL Server Indexes”.

If you are interested, to learn ALL about indexes, you must follow this series of posts.

http://www.sqlservercentral.com/articles/Stairway+Series/72284/

SQL Server Community: A Brief Introduction to SQL Server Denali

Microsoft SQL Server 2011, code-named 'Denali' (CTP) is available since 8th November, 2010 . Currently I came a cross following brief introduction of this version by talented guy Niladri Biswas a Lead Engineer at HCL Technologies Ltd and found it a must visit post.

An Introduction to Sql Server 2011 (Code Name Denali) –Part I (SSMS Features in CTP 1)