Feeds:
Posts
Comments

Archive for September, 2011

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 settings 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 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)

SSMS_Query_Governor

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

Advertisements

Read Full Post »

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 »

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 »

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 »

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 »

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 »

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

Solution to FizzBuzz Problem

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 »

Older Posts »