SQL-Server Blog of Ritesh Shah --Fight the fear of SQL with SQLHub.com

What are the important points to remember while setting up HADR in SQL Server 2022?

2024-05-02T12:24:00.001+05:30

It's been more than a decade I wrote an article that too on the LinkedIn....

Compatibility and Upgrade: Ensure that your SQL Server environment is compatible with SQL Server 2022 and plan for any necessary upgrades or migrations.

HADR Options: Explore the HADR options available in SQL Server 2022, such as Always On Availability Groups, Failover Cluster Instances, and Database Mirroring, to determine the best fit for your requirements and the SQL Server edition your company is owning.

Server and Network Redundancy: Set up redundant servers, storage, and network components to minimize single points of failure and ensure high availability.

Interesting!? Continue reading it at here.

blog.extreme-advice.com is new blog home for me

2012-10-16T22:41:00.000+05:30

SQLHub.com is my favorite domain name and I love SQL Server so I started this blog few years back. I still like SQL Server very much but now I am thinking to share my knowledge in SQL Server along with other technology too and that why I have booked new domain http://www.extreme-advice.com/ and started new technology blog at http://blog.extreme-advice.com/.

SQLHub.com will not have my new articles now so I would like to request my blog readers, please update your book mark with my new blog http://blog.extreme-advice.com/. You can even subscribe to new blog, there is text box given for subscription in http://blog.extreme-advice.com/ at center-right side of the blog.

You can even drop by face book page of extreme-advice.com. Have a look at Facebook page of Extreme-Advice.

Enjoy Reading !!!!

Reference: Ritesh Shah
http://Extreme-Advice.com
http://www.sqlhub.com
“Quick Learn“
Note: Microsoft Books online is a default reference of all articles

Extreme-Advice : Analyze IO disk pressure in SQL Server

2012-10-15T09:36:00.002+05:30

Before we move further, have a look at my following article as we are going to use the script given in that article:

Find Disk performance with sys.Dm_io_virtual_file_stats in SQL Server
I have already provided the script to display Disk IO waits in above given article, now we will further enhance that process.

By looking at IO Stall given in above article, can’t help us much. We should have data captured for few hours or may be a few days so that we can have details like which time/day we get high number of stall, we can find pattern of IO based on the statistics we capture for few hours/days. Now question comes into the picture, how can we do it with help of script given in “Find Disk performance with sys.Dm_io_virtual_file_stats in SQL Server” article?

Well we have to enhance the script give there. Let us start our journey to capture pattern of IO.

Click here to read complete article.

Extreme-Advice : Find Disk performance with sys.Dm_io_virtual_file_stats in SQL Server

2012-10-12T09:08:00.000+05:30

“sys.Dm_io_virtual_file_stats” is one of the excellent DMV in SQL Server. Generally people used talk about processor, memory and network a lot but always overlook disk capacity. You can’t read/write more than the capacity of your disk, no matter how many processor/core you have, how much RAM you assign to SQL Server or how fast your network is.
Whenever I have a situation to deal with performance issue of SQL Server, I never forget to use “sys.Dm_io_virtual_file_stats” DMV. I have very small script but it can save so many hours which you suppose to spend in finding a bottleneck of performance in SQL Server whenever disk is a culprit.

Each disk has capacity to read and write, if your workload is generating more read/write then the capacity of your disk, you can’t achieve optimal performance of other hardware you have.
Here is the script to measure disk IO capacity, have a look at it:

Click here to read complete article:

Extreme-Advice : Performance Counter in Microsoft Windows Server 2008

2012-10-12T09:05:00.001+05:30

Performance Counter in Microsoft Windows Server 2008 :

As I have promised in my article “Performance Counter in SQL Server“, herewith, I am providing introduction about how to run Performance Counter from Microsoft Windows?
As a Database Administrator or System Administrator, you always have responsibility to monitor system performance, find bottleneck of performance which may be related to network, disk, memory, IIS, SQL Server and much more. Each admin have different responsibility to monitor different part of system, depends of the domain s/he working one. These monitoring responsibility can greatly be done with help of Performance Counter as it cater the needs of DBA, System Admin, IIS Admin and much more.

You can create your own “Data Collector Set” in “Performance Counter” with the counters you need. You have variety of choices to select in form of counter. some of the important counters are as follows:

Click Here to read complete article.

Extreme-Advice : Performance Counter in SQL Server

2012-10-09T13:47:00.001+05:30

Performance Counter always helps DBA as well as system administrator to keep watch on performance of Operating System as well as SQL Server. There are thousands of performance counter offered by Windows but I mostly interested in some of the important performance counter related to SQL Server and hence I have created one view which I can use directly from SSMS or from one of web page for monitoring I have.
Keeping a watch on performance, especially in business hours , is one of the mandatory task for any DBA or system administrator. Herewith, I am sharing the TSQL script which can be helpful to any DBA or system administrator who wants to monitor performance of SQL Server & Database along with some of the crucial information about buffer & memory usage, lock & dead lock details and compilation-recompilation & caching.

You can run performance counter from GUI in Windows as well as from DMVs of SQL Server (Given in this article). Performance counter from GUI in windows give lot more counters then we are provided in DMV of SQL Server. Windows’ performance counter intend to keep an eye on every aspect of Windows not only SQL Server but DMV provided in SQL Server will have only those performance counter which are important and necessary to keep a watch on performance of SQL Server instance and its databases. I will cover Performance Counter from Windows GUI sometime later.

Click here to read complete article.

Extreme-Advice : HIPAA or EPA compliance SQL Server Database

2012-10-08T16:18:00.000+05:30

If you look around in IT market, you will find so many software product available for different type of laboratory like environment, pharmaceutical and many more. You will also find so many software product available for health care, hospitals, insurance and doctors. I have observed many times that while developing all these application, many of the US government agency rules are ignored. If you are selling your product in USA, you shouldn’t ignore the rules & regulation provided by agencies. EPA (Environmental Protection Agency) and HIPAA (Health Insurance Portability and Accountability Act) are two of the example of those agency who controls environmental laboratories and health care company respectively.

I have personally observed , in my few of the past consultation project , that Software company, many times, ignore rules given by these kind of agencies while developing product. This ignorance or lack of knowledge will fit you in critical situation along with the user of this software product. Not only HIPAA or EPA but almost each government agencies always enforce security for database. They will not allow any security breach in database. There may be different level of security and rules given by different agencies but my intention is to define some generic advice to make the database & SQL Server security little more tighten. According to me the security advice are given in this article should be regardless of which software product you are using/developing or which government agency is going to audit your environment. You can apply even more security then given in this article but these advice should be implemented first to make sure that your server is not exposed to security breaches.

Click here to read completed

Extreme-Advice : Find database restore date in SQL Server

2012-10-05T16:20:00.003+05:30

Last week I had a situation to check & confirm some data in one of the master table with current live database with two-week old data so obviously I have to restore two-week or older backup somewhere and check the data of the master table from restored database to current live database.

We have one development server and we used to restore live database there as and when needed so I wanted to check when was the live database restored in development environment last time. If database was restored in last 15 days, I have to take one of the two-week old backup and restore it in development environment. If development database wasn’t restored in last two weeks, I can directly check and compare data of master table and save my few hours. So I have developed one TSQL script which can give me the history of my database restore.

I have used some system table of MSDB database in SQL Server because backup/restore history of databases resides in MSDB database. Here is the list of system table resides in MSDB database which I have used to find database restore date in SQL Server along with its official definition given by Microsoft in BOL.

Click Here to read full article.

Extreme-Advice: Setup development environment for Windows Azure

2012-10-04T13:04:00.003+05:30

After explaining what is cloud computing? and essential part of Windows Azure in last few days, many people asked me how they can setup development environment for Windows Azure and how they can test or deploy the application they have created. I got overwhelming response, in form of messages, from the Facebook page of “Extreme-Advice” after keeping above two cloud articles there.

Well, there are many different tools and kits provided by Microsoft to setup your development environment for Windows Azure. Some of the tools are coming in one bundle at the same time you can install it individually as per your requirement. Most of the tools given here, would comes under one bundle itself, now a days , even I have mentioned few of the important part separately just to demonstrate which exact tools comes in use for which purpose.

Here, I will be providing with few of the mandatory tools which you will need to develop Windows Azure application.

Click here to read complete article.

Extreme-Advice.com: Calculate Table & Schema size in SQL Server

2012-10-03T15:50:00.003+05:30

Keeping watch on database size, drive size, table size, index size etc. are few of the primary duty of DBA. I have already written few articles to cover this type of subject, for example:

Keep watch on your disk size
Keep watch on your database size
Calculate size of index

Today I am going to introduce two more TSQL Script which covers the size of each Table and size of each Schema in your SQL Server database. I used to have log of table size for each date so that I can compare the growth of table for future disk & other resource planning. It is really very mandatory for DBA to know the actual size of each table along with total number or rows available in that table, in order to make operation smooth and successful.

Click Here to read full article.

Understanding Microsoft Cloud and its essential part

2012-10-02T07:23:00.001+05:30

After giving brief introduction of Cloud few days back I thought to give some light on more details of Microsoft Windows Azure. Microsoft Windows Cloud is created by so many different services and out of which, following are the some of the important services which makes Windows Cloud more fascinating.

Read full article, click here.

Extreme-Advice: What is Cloud Computing ?

2012-09-28T17:51:00.005+05:30

Well, I have received the question “What is SQL Azure?” many times in an email and in IT events I go. I generally write blog about SQL Server but I have not written anything about SQL Azure so far. Before I actually answer What the SQL Azure is, I have to explain a small bit about “Cloud” first.

To read complete article, click here.

Reference: Ritesh Shah

http://Extreme-Advice.com
http://www.sqlhub.com
“Quick Learn“

Note: Microsoft Books online is a default reference of all articles but examples and explanations

Extreme-Advice.com: Find Blocking in SQL Server and use it to send an alert

2012-09-25T16:28:00.000+05:30

As a DBA, I am always interested to find whether blocking is going on, in my environment or not? How long blocking exists? Which are the queries/SPs creating blocking etc.

Blocking generally occurs when one SQL Server connection (user process or application process) places a lock on a table or a number of rows and a second connection attempts to read or modify the data under the lock by first connection. Depending on the type of the lock, this can cause the second connection to wait until the first connection releases its lock. A blocked connection waits indefinitely for the blocking connection to release its lock.

Read complete article here.

Extreme-Advice: Write process in TRASACTION LOG (.LDF) file in SQL Server

2012-09-25T16:27:00.002+05:30

I have written one article about Transaction Log File (.LDF) yesterday, today I am presenting one more article on the same subject.

There is a myth going on in the mind of many developers that only DML statement like INSERT/ UPDATE & DELETE are being written in the log file but the truth is that SQL Server writes DDL operation like CREATE TABLE or CREATE INDEX etc. in the log file too.

Read complete article here:

Extreme-Advice: Transaction Log File (.LDF) in SQL Server along with ACID property and recover mode of database

2012-09-25T16:25:00.002+05:30

SQL Server databases created with two types of file:
Data File (File with extension .MDF/.NDF)
Log File (File with extension .LDF)

Data File can be one or more than one but physical log file will always remain one for each database. You have control over the size of log file but you can’t have more than one physical log file. There are so many mysteries and myth roaming around log file in developer’s mind so I thought to take an initiative to break this ice.

Read complete article here:

Extreme-Advice: Find locked table name with duration and count in SQL Server

2012-09-25T16:21:00.000+05:30

Finding LOCK table in SQL Server is one of essential task of DBA. Locked table for long time in SQL Server adversary affect on performance of the application, especially in pick business hours.

I have one SQL Server VIEW which help us to find all the table which are being locked currently along with duration since it is being locked.

Please read complete article here:

use of sys.dm_exec_requests and sys.dm_exec_sessions

2012-09-14T14:40:00.000+05:30

As a DBA, it is mandatory to keep an eye on your database and server too, especially during the business hour. SQL Server providing very helpful Dynamic Management View to help in this situation. Dynamic Management View (DMV) are as follow:

sys.dm_exec_requests
sys.dm_exec_sessions

Get the script of the SQL Server view, click on the below given link:

http://blog.extreme-advice.com/2012/09/14/find-currently-running-sessionprocess-in-sql-server-databaseinstance/

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations

SQLHub News: Interview of Ritesh Shah is published on PACKT website

2012-09-03T11:20:00.000+05:30

There is a big difference between writing a book and writing a blog. I understand this when I have started writing a book “SQL Server 2012 Performance Tuning Cookbook”.

As soon as PACKT proposed me to write the book on this subject, I have started deciding the topics to cover in this book with my co-author Bihag Thaker. It was a big challenge to decide the content of this book because performance tuning is a big topic and covering all aspects under the one book is little difficult but finally we have managed the book in three following parts.

1.) Performance Monitoring

2.) Performance Tuning

3.) Performance Management

Each topic covered the practical recipe for hands-on experience of the readers. PACKT has conducted my interview regarding my journey of this book writing process. Further reading the interview, please click here.

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Implementing physical Database Structure

2012-07-28T23:01:00.002+05:30

Our book “SQL Server 2012 Performance Tuning cookbook” is published this week. This book is based on the concept of performance tuning in SQL Server 2012. We have one chapter “Implementing physical Database Structure” in our book “SQL Server 2012 Performance Tuning cookbook”. Following article is completely based on the chapter we have in our book “SQL Server 2012 Performance Tuning cookbook”.

Database structure is one of the important factor which affects performance of your database. Your database performance heavily depends on how you have physically placed your database objects and how you have configured your disk subsystem. Designing the physical layout of your database correctly is the key factor to improve the performance of your database queries and thus the performance of your database. However, the correct decision on a physical design structure of the database depends on the available hardware resources that you might have. This includes the number of processors, RAM, and storage resources, such as how many, disks or RAID controllers you might have in your database environment. The best thing while designing physical layout of the database is to have multiple physical disks for your database. If you configure your database in such a way that it spreads across multiple disks, it can benefit from parallel I/O operations.

The following are some of the decisions that influence your database performance:

Where do you place data files?
Where do you place log files?
Where do you place large objects?
Where do you place indexes?
Where do you place the tempdb database?

You can control the physical location of database objects within the database by using files and filegroups.

To read complete article kindly click here.

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

350 Article on the blog and SQL Server 2012 book release

2012-07-27T07:07:00.003+05:30

I have completed 350 articles on this blog and apart from that my book “SQL Server 2012 Performance Tuning Cookbook” has released yesterday officially.

I would like express my deepest gratitude to all my blog readers and online SQL Server community which always inspire me to work hard and returning back to the community.

In today’s blogpost I am not going to discuss any technical stuff but I would like express my truthful thanks to few person who has always played an important role in my life. Though I have expressed my thankfulness to them in the “Acknowledgement” section of my book, I can’t stop my temptation to express my gratitude blogpost.

I would sincerely like to thank Packt Publishing, for showing their confidence in me and providing the invaluable opportunity of being a part of this book. Individuals at Packt whom I am deeply grateful to, are Kedar Bhat, Sai Gamare, Madhuri Das, Ashwin Shetty, Apoorva Bolar, and Dhwani Devater. They have been very co-operative and supportive at all the stages of this book. I am extremely thankful to Michael Zilberstein (Blog) and Maria Zakourdaev (Blog, Twitter) and Satya (Blog,Twitter) the technical reviewers, for their excellent work of getting the accuracy of the technical details of the book in perfect shape.

I wouldn't have been able to author this book alone, so thanks should go to Mr. Bihag Thaker, as well, as he agreed to co-author this book with me and has worked even harder on it than I have myself.

Two individuals to whom I am indebted and whose disciple I have always been are Mr. Pinal Dave(Blog, Twitter) and Mr. Jacob Sebastian (Blog, Twitter). I have learnt a lot from them, and they are the reason I'm part of the IT community today.

1.) Pinal Dave, who blogs at SQLAuthority.com and is an author of several SQL Server books. Currently, he is working as a Technology Evangelist at Microsoft.

2.) Jacob Sebastian, who blogs at BeyondRelational.com and is a SQL Server MVP, book author, well-known speaker in SQL Server technology, and much more.

Without my family support, a task such as writing a book would not have been achievable. I would like to heartily thank my parents, Mr. Ashwinkumar Shah and Mrs. Divya Shah. It is because of them that I exist, and I cherish their blessings, which are always with me. I would also like to thank my one-and-a-half-year-old son, Teerth, who used to often start crying at midnight, because of which I would lose my sleep and, not being able to get it back so started researching more on the subjects that helped me write this book. Last but not least, I would like to thank my wife, Alka Shah!

Finally I would appreciate help and support of all my friends who has directly and indirectly helped me a lot.

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Find SQL Server job ran on specific date with its status

2012-07-25T07:40:00.000+05:30

It is very mandatory for SQL Server DBA to keep an eye on the SQL Server Job and take an immediate action if job gets failed. There are several ways of doing this. I will be showing one query which will return all jobs ran on the specific date along with its status like whether it was failed or succeed.

In order to gain information regarding JOBs, we have to query following list of system tables in MSDB database because all SQL Server jobs are stored in MSDB database only.

1.) Sysjobhistory

2.) Sysjobs

3.) sysjobsteps

Here is the query which will return the desired result:

USE MSDB

DECLARE @Today AS VARCHAR(8)

SET @Today = CONVERT(VARCHAR(8),GETDATE(),112)

SELECT * FROM (

SELECT

SysJob.name,

CASE WHEN JobHist.run_status =1

THEN 'Success'

WHEN JobHist.run_status =0

THEN 'Fail'

END AS JobRunStatus,

JobHist.run_date,

JobHist.run_time,

JobHist.run_duration AS RunDuration,

JobStep.step_id,

JobStep.command,ROW_NUMBER() OVER(PARTITION BY SysJob.name,JobStep.step_id ORDER BY run_time DESC) AS NumberOfExecution

FROM

dbo.sysjobhistory AS jobHist

INNER JOIN

dbo.sysjobs AS SysJob

JobHist.job_id = SysJob.job_id

INNER JOIN

dbo.sysjobsteps AS JobStep

(JobStep.job_id = SysJob.job_id)

WHERE

JobHist.run_date = @Today

)

AS T

WHERE

NumberOfExecution=1

As a DBA, I keep this script handy as I never know when I will need this. We already have monitoring over each and every jobs and failure of any jobs will be notified to me via email though this has become time saving for me so many times.

One tip I would like to give especially when I am talking about JOB is, keep regular backup policy for MSDB database too as all the JOBs are being stored here and while restoring SQL Server from any disaster, we need to create all the JOBs again and MSDB database backup become life savior in that case.

I have explained the importance of backup in one of my past article, if you are interested to read, click here.

Happy Scripting!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

TableDiff utility to compare data between two databases

2012-07-18T08:36:00.004+05:30

Introduction

As a database professional, we might come across in situation where we need to compare data row by row or column wise between two tables which either resides in same database or in same instance or may be in different instance in different server.

What do you in this situation?

1.) Do you write down script of your own?

2.) Do you use any third party software?

3.) Do you use “TableDiff” utility comes with SQL Server itself?

Third option, out of all of the above, seems good to me as we neither need to invent the zero again by writing down the script by our own nor we need to pay extra money to compare data.

“TableDiff” is one of the wonderful and oldest utility provided by Microsoft. It works fine with SQL Server 2000 to the latest SQL Server edition. However, I am providing you the script and example from my SQL Server 2008 instance.

Getting Ready

Before you move forward, you need to find out two tables whose data you wanted to compare. It might be in publisher/subscriber in replication, it might be in two different databases you are using for scale out or may be anywhere else.

If you don’t have this situation at the moment in your environment, don’t worry, I will be giving a script to raise the scenario to test “TableDiff” utility.

How to do it...

1.) Open New Query window in you SQL Server

2.) Create two different database by using following script:

USE master

CREATE DATABASE TableDiffDb1

CREATE DATABASE TableDiffDb2

3.) Create a sample table in “TableDiffDB1” database with following script

USE TableDiffDb1

--if orders table is already there. you can delete it than create new one with name "Orders"

IF OBJECT_ID('orders', 'U') IS NOT NULL BEGIN

DROP TABLE orders

END

--creating table

CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)

--inserting 1000 sample rows into table

INSERT INTO orders (OrderDate, Amount, Refno)

SELECT TOP 1000

DATEADD(minute, ABS(a.object_id % 50000 ), CAST('2010-02-01' AS DATETIME)),

ABS(a.object_id % 10),

CAST(ABS(a.object_id) AS VARCHAR)

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

4.) Creating “Orders” table in second database by copying 900 records (out of total 1000 records) from “Orders” table from “TableDiffDB1” database by using following script.

USE TableDiffDb2

--if orders table is already there. you can delete it than create new one with name "Orders"

IF OBJECT_ID('orders', 'U') IS NOT NULL BEGIN

DROP TABLE orders

END

--creating table

CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)

--inserting 900 sample rows into table from TableDiffDb1 database's Orders table

INSERT INTO orders (OrderDate, Amount, Refno)

SELECT TOP 900 OrderDate,Amount,Refno FROM TableDiffDb1.dbo.orders

5.) Now use following command to see the difference between two tables.

exec master..xp_cmdshell 'tablediff -sourceserver [RITESH-SHAH\MSSQL2008] -sourcedatabase TableDiffDb1 -sourcetable Orders -destinationserver [RITESH-SHAH\MSSQL2008] -destinationdatabase TableDiffDb2 -destinationtable Orders -et Difference -f D:\OrdersDifference.sql'

Replace your server instance name in “SourceServer” and “destinationServer” parameter in above given command and you will get one .SQL file in D drive. Running that SQL file will insert all missing records in “Orders” table of “TableDiffDb2” database as it shows you the list of all missing records there.

There's more...

I would like to draw your attention to some of the facts which can help you if you don’t find “TableDiff” working in your environment.

Remember that “TableDiff.exe” file resides in installation directory of SQL Server by default which is “C:\Program Files\Microsoft SQL Server\100\COM” in my case. So, there is chance that “TableDiff” command is not accessible via DOS prompt, you have to set path for “TableDiff” in “ServerVariable”.

You can reach “ServerVariable” by “MY Computer Properties > Advanced System Settings > Advanced > Environment Variables > System Variables > PATH”

If you find any path under “PATH” in “ServerVariable”, you can put “;” (semicolon) after that path and can add your path for “TableDiff”.

Generally people tend to use “TableDiff” from DOS prompt itself or via .bat (batch file) file but I have used “xp_cmdshell” extended stored procedure to show the use of command right from SQL Server but there may be a chance that “xp_cmdshell” is disable in your environment. If your security constraint allows, you can enable “xp_cmdshell”. For more details about the steps, click here.

Reference: Ritesh Shah

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Training – useful or useless?

2012-06-06T14:59:00.002+05:30

My very good friends and SQL Server icon Mr. Pinal Dave wrote 5 articles on this wonderful topic “Training”. I often see debate among developer whether training is useful or useless but according to my personal opinion, Training plays very important role in the life of professional so let us not discussed whether it is really useful or useless as Pinal has already had so many details in his 5 articles and I am sure it will change your attitude towards training if you are not so much positive towards training.

Training enhances skill level of professional. Though a benefit of training is intangible but investing in training benefits both, employee as well as organization.

In today’s age there is rapid technology innovations comes into the market and making the employee trained for those innovations ultimately benefits company as well as employee both in order to stand in market with cut edge technology.

Learning & Training are the processes, any human follows lifetime. At age of toddler, we are being trained from family regarding how to eat, how to walk, how to talk etc. In the kindergarten we are having social training for our transition from home to school then schooling and college and so on……….

In short, training is a never ending process in life and always try to learn from each and every person you meet in life. Here is the list of 5 articles written by Pinal Dave, I highly recommend reading it!!! believe me those are really awesome articles:

Developer Training – Importance and Significance – Part 1
Developer Training – Employee Morals and Ethics – Part 2
Developer Training – Difficult Questions and Alternative Perspective – Part 3
Developer Training – Various Options for Developer Training – Part 4
Developer Training – A Conclusive Summary- Part 5

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

“Max degree of parallelism” or “MAXDOP”, good guy or bad guy?

2012-05-05T13:05:00.000+05:30

Introduction

Yesterday I have written article for NOLOCK in good guy or bad guy series. Today I am presenting one topic on the same subject which is “max degree of parallelism”. This is also one of the topics which can raise the big debate on. Now a days people used to have powerful servers with multiple processor for processing their database request and they tend to believe that more processor can perform well always to process the query which is a myth but in reality more processor can performs well for some query but perform really worst for others too so rather then setting up a fix figure to process each query, I believe to leave the decision on SQL Server as SQL Server 2012 has such a smart engine which can decide whether to go for parallelism or not.

As long as my personal practise concern, I used to use this option at very last resort as most of the query performance can be done via following:

Proper Index
Properly written query which can use index
Updated statistics

Getting ready

I am going to perform this example in my SQL Server 2012 RTM version but it may work as it is in SQL Server 2005 / 2008 too.

Deciding the processor for the query could be done via following two popular ways:

· Set server wide value with option “max degree of parallelism” via “sp_configure” stored procedure

· Use the MAXDOP hint

In this article I will show both ways in coming sections.

We are going to use “orders” table in this article, you can get the script to generate that table from previous article, click here.

How to do it...

1. After connecting to SQL Server Management Studio (SSMS), open new query window.

2. Set the 0 for “max degree of parallelism” in “sp_configure” system stored procedure with following T-SQL:

--enable advanced option in sp_configure procedure

EXEC sp_configure 'show advanced option',1

RECONFIGURE WITH OVERRIDE

--setting 0 for max degree of parallelism

--0 is the default value so even by executing

--following command, we are not making any change

sp_configure 'max degree of parallelism', 0

RECONFIGURE WITH OVERRIDE

3. Before we test any query let us clear cache and buffer so nothing will be used from saved execution plan. I highly recommend using following command in development or testing server only. Clearing cache and buffer in production server may give you slow performance temporarily.

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

4. Now specifying parallelism at query level with MAXDOP hint by executing following T-SQL

set statistics IO on

set statistics time on

select * from orders option (MAXDOP 1)

set statistics time off

set statistics IO off

set statistics IO on

set statistics time on

select * from orders option (MAXDOP 2)

set statistics time off

set statistics IO off

5. Since we had “Statistics IO” and “Statistics Time” enabled while executing both of the above SELECT query. We can get results of SET commands in “Message” tab besides “Results” panel. Here is the result I am getting in my testing server, you might get different result.

(100000 row(s) affected)

Table 'orders'. Scan count 1, logical reads 409, physical reads 0, read-ahead reads 414, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 16 ms, elapsed time = 586 ms.

(100000 row(s) affected)

Table 'orders'. Scan count 1, logical reads 409, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 62 ms, elapsed time = 575 ms.

How it works...

Setting value 0 (zero) either in MAXDOP or for “max degree of parallelism” indicate that SQL Server has choice to decide whether to use multiple processor for the query or not. Apart from 0, any number specified more then 0 indicate that, maximum upto that number of processor could be used by processor.

We have used MAXDOP 1 for first SELECT query and MAXDOP 2 for second SELECT query and you can see results of “STATISTICS TIME” that one processor has done the better job by consuming 16ms in CPU time as against 62ms CPU time in second try where we had MAXDOP 2 which is indeed a big difference.

CPU time shows the time it has taken to process the query.

Conclusion

If there is no other alternative, then and then go for selecting the processor for your query otherwise let SQL Server take decision by its own.

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

NOLOCK hint in SQL Server, Good guy or bad guy?

2012-05-04T16:43:00.000+05:30

Introduction

It is quite often in production environment that multiple application and/or users try to access same resource at the same time which may create locking and blocking issues. It is tricky to deal with this situation as there is no rocket science which suits for all situations. It is all depends on situation because in some production environment, it is not possible to resolve blocking issues easily over the night.

The main root cause for locking is, we have long running transaction which keeps your object locked and meanwhile any request comes to access the same object, has to wait until the current transaction complete its operation. Best choice should be to minimize the transaction length so that it releases the lock quickly and other request doesn’t need wait due to lock but unfortunately it is possible to solve it easily in ALL environments.

Getting ready

I am going to perform this example in my SQL Server 2012 RTM version but it may work as it is in SQL Server 2005 / 2008 too.

Connect to your SQL Server and open three query window. We will call these query windows with following name:

· Win1

· Win2

· Win3

How to do it...

1. After connecting to SQL Server Management Studio (SSMS), open Win1

2. Create one sample database, named SQLHub and create one table with sample rows with following T-SQL script:

create database SQLHub

USE SQLHub

--if orders table is already there. you can delete it than create new one with name "Orders"

IF OBJECT_ID('orders', 'U') IS NOT NULL BEGIN

DROP TABLE orders

END

--creating table

CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)

--inserting 100000 sample rows into table

INSERT INTO orders (OrderDate, Amount, Refno)

SELECT TOP 100000

DATEADD(minute, ABS(a.object_id % 50000 ), CAST('2010-02-01' AS DATETIME)),

ABS(a.object_id % 10),

CAST(ABS(a.object_id) AS VARCHAR)

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

3. Execute one UPDATE statement in Win1 with BEGIN TRANSACTION. Note that we don’t have COMMIT or ROLLBACK after the UPDATE Statement at the moment. ROLLBACK is commented so it won’t be executed.

BEGIN TRANSACTION

UPDATE Orders SET Amount=5.00 WHERE OrderID<10

--ROLLBACK

4. In Win2, try to execute following SELECT statement:

select * from Orders Where orderID<=15

5. You will observe that SELECT statement wouldn’t return any results so in WIN3, try to execute following T-SQL and know what is going on behind the screen:

sp_who2

6. We will get many rows in the result set from the above command but we have to look at last two rows of SQLHub database as per given in following screen capture:

7. We can see that out UPDATE statement runs with SPID 54 and SELECT statement runs with SPID 55. SELECT query is blocked by SPID 54 given in row no.2 column no.5 in screen capture. Now I have two ways. Either I issue COMMIT / ROLLBACK command which is not a good idea to interrupt UPDATE or cancel SELECT query in SSMS which I will do now.

8. Since we have cancelled SELECT query, we will not execute same SELECT statement with NOLOCK hint.

select * from Orders WITH (NOLOCK) Where orderID<=15

9. Here is the result return by above query.

10. Go to Win 1 and execute “ROLLBACK” statement.

11. Go back to Win 2 and execute SELECT statement with or without “NOLOCK”. For eg:

select * from Orders Where orderID<=15

12. Here is the screen shot of result return by above query:

How it works...

When we have executed UPDATE statement in Step no 3 without COMMIT or ROLLBACK, It updates the records but didn’t release the lock it has acquired on the table so SELECT query was not able read data and return it.

How do you know that whether UPDATE has really updated the values or not? Since our select query is not giving results. We have executed same select query with “WITH (NOLOCK)” hint in step no. 8 and we can confirm that values are update with the screen capture given in step 9. Isn’t it good situation? Yes, may be as we were not even able to get the result of SELECT statement in step no 4. But I would say we can’t decide whether it is a good or bad without evaluating the business need.

Since UPDATE statement has updated the value but it is not saved so there is a chance to ROLLBACK. If you see resultset given in steps no 9, you will see “5.00” in “Amount” column which may not be a proper value as after ROLLBACK, it comes back to “2.00” again as per step no. 12.

Think if this would be a result of stock exchange, what would happen? Can we run with dirty read we seen in screen shot in step no.9? No certainly not that is why I have conveyed that NOLOCK is all depends on the business need and situation. Use it wisely as it is two sided sword.

SQL-Server Blog of Ritesh Shah --Fight the fear of SQL with SQLHub.com

What are the important points to remember while setting up HADR in SQL Server 2022?

blog.extreme-advice.com is new blog home for me

Extreme-Advice : Analyze IO disk pressure in SQL Server

Extreme-Advice : Find Disk performance with sys.Dm_io_virtual_file_stats in SQL Server

Extreme-Advice : Performance Counter in Microsoft Windows Server 2008

Extreme-Advice : Performance Counter in SQL Server

Extreme-Advice : HIPAA or EPA compliance SQL Server Database

Extreme-Advice : Find database restore date in SQL Server

Extreme-Advice: Setup development environment for Windows Azure

Extreme-Advice.com: Calculate Table & Schema size in SQL Server

Understanding Microsoft Cloud and its essential part

Extreme-Advice: What is Cloud Computing ?

Extreme-Advice.com: Find Blocking in SQL Server and use it to send an alert

Extreme-Advice: Write process in TRASACTION LOG (.LDF) file in SQL Server

Extreme-Advice: Transaction Log File (.LDF) in SQL Server along with ACID property and recover mode of database

Extreme-Advice: Find locked table name with duration and count in SQL Server

use of sys.dm_exec_requests and sys.dm_exec_sessions

SQLHub News: Interview of Ritesh Shah is published on PACKT website

Implementing physical Database Structure

350 Article on the blog and SQL Server 2012 book release

Find SQL Server job ran on specific date with its status

TableDiff utility to compare data between two databases

How to do it...

There's more...

Training – useful or useless?

“Max degree of parallelism” or “MAXDOP”, good guy or bad guy?

Introduction

Getting ready

How to do it...

How it works...

Conclusion

NOLOCK hint in SQL Server, Good guy or bad guy?

Introduction

Getting ready

How to do it...

How it works...

See also