Use Query Governor to Prevent Long Running Queries

Today I learned about a new instance level option, its called Query Governor. Looks like its been an available feature for a while, but I never heard of it before. This setting allows you to prevent any queries running longer than a predetermined set value. Any query that has a running cost value more that a set number (Cost measured in seconds).

You can set it two ways. T-sql and SSMS.

1. T-Sql: Since this option is an advanced option, first we must set ‘show advanced options’. Then RECONFIGURE to be able to set ‘query governor cost limit’ value

USE MASTER
go

EXEC sp_configure 'show advanced options', 1
GO

RECONFIGURE
GO

SELECT * FROM sys.configurations
WHERE name = 'query governor cost limit'
GO

Settings value before:

Query governor cost limit (before)

USE MASTER
go

EXEC sp_configure 'query governor cost limit' , 100   -- 100 seconds
GO

RECONFIGURE
GO

SELECT * FROM sys.configurations
WHERE name IN ('query governor cost limit')

EXEC sp_configure 'show advanced options', 0
GO

RECONFIGURE
GO

Settings value after:

Query governor cost limit (after)

Also, you can set this value per connection using SET command:

SET query_governor_cost_limit 100

2: Right click on server >> properties >> Connections >> Check box (Use query governor to prevent long running queries)

Query Governor in SSMS

Points to keep in mind:

1. Before you activate ‘query governor’ setting in your production environment, get an estimate of the cost of queries on your production system.

Hope this helps,

_SqlTimes

Read Full Post »

Compare features in different High Availability options in Sql Server.

Since Sql Server 2005, High Availability (HA) has become very easy to implement in a Sql Server environment. Also, starting Sql 2005, there are 4 methods to implement HA.

• Failover Clustering
• Database Mirroring
• Replication
• Log Shipping

Though, Failover clustering still is the most popular option, my guess is the other options might make it a more level field going forward.

In the following matrix, I compare some features of each HA option in one table. This gives a good comparison picture of the features available in each option.

Property \ Feature	FailoverClustering	DB Mirroring	Replication	Log Shipping
Server Level	Yes	No	No	No
Database Level	No	Yes	Yes	Yes
Automatic Failover	Yes	Yes (Only HA Option)	No	No
Automatic Failure Detection	Yes	Yes (Only in HA option)	No	No
Hot, Warm or Cold standby	Hot	Hot and Warm Options	Warm	Warm
Server Level Objects Failover	Yes (Automatic)	Manual	Manual	Manual
Is data on Failover available for queries while the Primary server is running?	No	Yes (use Database Snapshots)	Yes	Yes (Standby Mode)
Are changes allowed at Failover location?	No	No	Yes (Merge and TRN Replication with updating subscribers)	No
Is Main and Failover server in a 1-1 relationship?	Yes	Yes	Not Required	Not Required
Is 1-Many relationship possible between the Main and Failover servers?	No	No	Yes	Yes
Is Many-1 relationship possible between Main and Failover server?	No	No	Yes	No
Is Automatic Application redirection possible after failover?	Yes	Yes	No	No
Recovery model required for the participating databases?	Any	FULL	ANY	FULL & BULK
Can you filter what data is sent to the Failover server?	No	No	Yes	No
Where does the logic HA reside?	OS + Sql	Sql	Replication Agents (they run outside Sql Server	Sql Agents (Run outside Sql Server)
If you want a reports server, what is the best solution?	Not Possible	Works, but needs constant refresh of the Snapshot database for latest data.	Best Option.	Works, but users connections need to be kicked out, when new log is applied.
Best solution if the Main and Failover server are separated by long distance?	Not good.	Works, but not recommended.	Works, but with high latency	Best Option. (Log compression reduces latency significantly.

Note: Please know that this is not all the features, these are just he ones I could think of off the top of my head. If you add your comments below, I can add them to the list.

Hope it helps,

_Sql Times

Read Full Post »

Use Backup Stripe to improve backup performance

Recently, I used this very powerful backup method and have seen significant improvement in backup speed. So I wanted to share it with everyone.

When I did backups, I used to take entire backup to one backup disk location using the standard BACKUP command shown below. If the database is large the backup process could take longer.

BACKUP DATABASE AdventureWorks
TO DISK =N'H:\Backups\AdventureWorks\AdW_01012011.BAK'
WITH STATS = 10

Sql Server backup process is very scalable. The only limitation to it is the speed of the backup device, i.e. how fast it can read/write data. So even if you have a RAID disk drive, its speed is limited. And when you have a large database ranging more than 500 GB to multiple TB’s, FULL backups can take much longer to complete.

So stripe backup can help you cut the backup time by many folds. If you have multiple drives attached to the Server, then we could essentially stripe the backup to separate disks and take advantage of the speed of multiple hardware devices to cut short the backup time. Backup process makes sure of stripe, almost, equal amount of data to each location.

BACKUP DATABASE AdventureWorks
TO DISK =N'H:\Backups\AdventureWorks\AdW_01012011_1.BAK'
, DISK = N'I:\Backups\AdventureWorks\AdW_01012011_2.BAK'
, DISK = N'J:\Backups\AdventureWorks\AdW_01012011_3.BAK'
WITH STATS = 10

Note: In some cases, Backup Striping could cause complexity to existing BACKUP/RESTORE procedures. Now you have to remember where each part of the backup file is located, so this technique is not for everyone. Its only for large databases where the benifit our weighs the complexity.

Thought not very popular we could do the same for BACKUP LOG command, as shown below:

BACKUP LOG AdventureWorks
TO DISK =N'H:\Backups\AdventureWorks\AdW_01012011_1.TRN'
, DISK = N'I:\Backups\AdventureWorks\AdW_01012011_2.TRN'
, DISK = N'J:\Backups\AdventureWorks\AdW_01012011_3.TRN'
WITH STATS = 10

Hope this helps,
_SqlTimes

Read Full Post »

Sql Server 2008 new features Compression, TDE and FILESTREAM compatibility with each other.

In previous two posts, (comparison with HA and other features) we looked at the new features compatibility with other existing features like Replication, Database Mirroring, Database Snapsho, etc.. Now we look at their compatibility with each other.

• Can you configure FILESTREAM filegroup on a database set of Compression?
• Is Compression allowed on a database encrypted with TDE?
• Can you create FILESTREAM filegroups on a database encrypted with TDE?

Lets look at the matrix below for answers:

	Compression	FILESTREAM	TDE
Compression	1	NTFS Only	N
FILESTREAM	NTFS Only	1	N
TDE	N	N	1

As you can see, TDE is not compatible with either Compression or FILESTREAM. A database set of Compression cannot be encrypted with TDE and vice versa. Since Compression requires Disk Cluster size to be 4096 bytes you cannot compress FILESTREAM data. For FILESTREAM data, the recommended Disk Cluster is 64K. I would recommend any one implementing FILESTREAM to read Paul S. Randal’s (SQLskills.com) article on MSDN

“The recommendation for using FILESTREAM is that the individual units of BLOB data be 1 MB in size or higher. If this is the case, it is recommended that the NTFS cluster size for the FILESTREAM data container volume is set to 64 KB to reduce fragmentation. This must be done manually as the default for NTFS volumes up to 2 terabytes (TB) is 4 KB. This can be done using the /A option of the format command. For example, at a command prompt type:

          format F: /FS:NTFS /V:MyFILESTREAMContainer /A:64K”

Why is it this way and all other implementation details will be covered in future articles.

Hope this helps,
_SqlTimes

Read Full Post »

Sql Server 2008 new features TDE, FILESTREAM and Compression compatibility with other features.

In the previous post we saw the compatibility of new features like TDE, FILESTREAM and Compression with High Availability (HA) options. Now we continue the compatibility matrix with other existing features.

	Compression	FileStream	TDE
Database Snapshot	Y	No	Y
Full Text Indexing	Y	Y	Y

Note: Sql Server does not support Database Snapshot for FILESTREAM filegroups. But you can create Database Snapshot for the rest of the non-FILESTREAM filegroups. If you try to query FILESTREAM data from the Database Snapshot you get the following error.

Could not continue scan with NOLOCK due to data movement.

When you look at any set of features compared in a matrix like this, it gets easier to remember.

In future, I will try to add details about each matrix cell.

Hope this helps,
_SqlTimes

Read Full Post »

Sql Server 2008 new features Compression, FILESTREAM and TDE compatibility with with all High Availability (HA) options

Some of the new features in Sql Server 2008 are very useful (and long over due). Features like

• Compression
• FileStream
• TDE (Transparent Data Encryption).

Though they have their own limitations, they are very useful when available natively.

Here we’ll look into their compatibility with existing High Availability (HA) features.

• FailOver Clustering
• Database Mirroring
• Replication
• Log Shipping

Lets start with the matrix, and then we’ll go into details:

	Compression	FileStream	TDE
FailOver Clustering	Y	Y	Y
Database Mirroring	Y	No	Y
Replication	Y	Y	Y
Log Shipping	Y	Y	Y

When you look at it this way, it makes it much easier to remember all the options. Of all, as you can see, only one stand out i.e. FileStream is not configurable in Database Mirroring. All the other configurations are fairly well supported. Please know that there are, in fact, some limitations for these features in each HA implementation (but, that is for an article some other day).

Hopefully, future versions of Sql Server will support FileStream in Database Mirroring.

In future, I will try to add implementation details about options in each cell.

Hope this helps,

_SqlTimes

Read Full Post »

DBA Interview Question

A few days ago, I saw a set of good interview questions to ask a DBA. I want to post answer to “10. Solve the FizzBuzz problem with SQL code“
DECLARE   @n  INT = 25
;WITH MyCTE (Num)
AS
(
SELECT 1 AS [Num]
UNION ALL
SELECT Num + 1 AS [Num]
FROM MyCTE
WHERE Num < @n
)
--SELECT * FROM MyCTE
SELECT Num
, CASE
WHEN (Num % 3 = 0) AND (Num % 5 = 0) THEN 'FizzBuzz'
WHEN Num % 3 = 0 THEN 'Fizz'
WHEN Num % 5 = 0 THEN 'Buzz'
ELSE CONVERT(VARCHAR, Num)
END AS [Mod3And5]
FROM MyCTE
GO




Comment on the rest of the question: Though the questions, at first, seem not technical, I feel that the idea behind these questions is important. You can know a lot about a Senior DBA through these questions than just asking them the regular technical questions. If I am interviewing a Senior DBA, I want to know their thinking style, their approach to problems more importantly than knowing answer to something like “What RECOVERY model is required to set up database mirroring on a database?”. You can always look up BoL for these when you are actually setting up DB Mirroring, but you cannot look up ‘thinking’‘.

Hope this helps,

_SqlTimes

Read Full Post »

Generate Numbers in a Sequence using one T-Sql Query

I have always found myself in need to generate numbers in a sequence. But as a DBA, I did not want to do it procedurally with a ‘WHILE loop with increment’. So I wrote this cool script that uses receurice CTEs. Hope it is helpful to you as it’s been for me.

DECLARE @n INT = 25

;WITH SeqNumbers (Num)
AS
(
SELECT 1 AS [Num]
UNION ALL
SELECT Num + 1 AS [Num]
FROM SeqNumbers
WHERE Num < @n
)
SELECT * FROM SeqNumbers
GO

See you next time with more tips,

_SqlTimes

NOTE: Sql Server, by default, allows only 100 recursions. But configurable.

Read Full Post »