Parallelism is a powerful feature in SQL Server designed to allow greater bandwidth for high impact queries. Most of the time however, I’ve seen too many queries qualifying for parallelism and essentially bringing the SQL Server box to it’s knees. To understand parallelism, we need to understand SPIDs (Server Process ID’s). These SPID’s act essentially as threads on a SQL Server. Normally there is one SPID assigned to each database connection. Sometimes, if the query is determined to be expensive, a SPID can be split out into multiple execution threads. This is known as parallelism. Cost Threshold for Parallelism Prior to breaking a SPID out into multiple threads, there is a cost determination. This is called the cost threshold for parallelism (set in seconds) and is set at the server-level. The idea is to allow parallelism when a query is estimated to run over a certain number of seconds. Generally, the determining factor is the estimated number of rows that will be returned either in the main query or an inner query within the execution plan. To set parallelism right click on the Server Instance in Management Studio. And select “properties”. Once properties is selected, a new window pops up displaying a more detailed server settings. We want to select on “Advanced” Now, in the lower middle of the screen we see the settings for Parallelism. The main two we are concerned with the is the cost threshold and the Max Degree of parallelism. What Settings to Choose? You need […]
Continue reading ...
Change Data Capture (CDC) is a powerful feature included in SQL Server 2008. Using change data capture, it is possible to determine which values have been added, changed, or deleted within a table. Setting up CDC is done by specifying tables to monitor. Under the hood, CDC is written using the same repl logreader function that transactional replication uses. Don’t worry too much though. If you are like me, then when you hear the word replication, you start running for the hills. Well, CDC is not as much maintenance. The biggest thing you have to worry about with CDC, is that the disk that contains the log never gets full. When that does happen, and it should be rarely, then yes it is a pain. What you have to do in that case is flip the do not truncate bit for the log file to ‘no’. That aside, let’s get started. First thing you want to do is enable change data capture. This is done at the database level. [cc lang=”sql”] USE AdventureWorks GO DECLARE @ReturnCode int EXEC @ReturnCode = sys.sp_cdc_enable_db SELECT @ReturnCode SELECT name ,is_cdc_enabled FROM sys.databases [/cc] Even though we performed the above action, nothing will happen until we define the tables and columns that it should monitor. When defining a table to monitor, there are a few parameters that need to be passed: CDC Parameters Parameter Description @source_schema The schema name of the table to monitor (ex: dbo) @source_name The name of the table to monitor @role_name […]
Luckily, two of the internal Microsoft tools used in order to diagnose slowdowns is also available for the publics use. These tools are SQLDiag (previously PSSDiag), and SQL Nexus. SQLDiag (short for SQL Diagnostics) monitors all aspects of the system that SQL Server is running on and generates all output needed in order to determine the bottleneck. The difficult challenge that follows is how to interpret that data. This is where SQL Nexus comes in. SQL Nexus is an application written on the .NET framework, that utilizes a client distribution of Reporting Services in order to give us insight into the output generated from SQLDiag. While it is relatively hopping along on 3 legs, we are still very lucky to have it. This video walks you through the initial process of setting up the traces and gives a brief intro in the data you can expect to find. In order to download, following this link and read the installation carefully. [code] http://sqlnexus.codeplex.com/ [/code] The installation guide will also walk you through downloading the ReadTrace tool (contained in the Microsoft RML tools download). And it will also tell you to download the PerfStats scripts which are used in order to initiate the SQLDiag trace. These are also important.
Continue reading ...
Browsing along the SQL Server documentation today, I noticed something that scared me. As a spoiled non ANSI compliant SQL Server developer, I do not use a semicolon to terminate my statements. Well, here’s the excerpt that threw me a curveball: Transact-SQL statement terminator. Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version. Here is the link: http://msdn.microsoft.com/en-us/library/ms177563.aspx Holy Crap! My first thought was about all the code I have written and how I could automate inserting semicolons to the end of all the statements. While that would be almost futile, I suppose it could be done. I’m sure Microsoft will provide a tool that will append a semicolon for the future release however. If they did not, it would stop people from upgrading, which would cost them money. This realization made me think more about how I code and how much more important it is to enforce ANSI standards into your work. Being shielded from these subtleties in the past has not been a benefit. You can now tell that this SQL Server developer team is really serious about their product. First they got rid of the NON ANSI joins, which was a big achievement in my view, now they are moving onto the semicolon. I can’t say it’s a big surprise because if you have ever used common table expressions, you’ll know that’s where the first indication of requiring a semicolon was put into […]
Here is the maximum value for a datetime datatype in SQL Server: 9999-12-31 23:59:59.997 And the minimum: 1753-01-01 00:00:00.000 The maximum precision for a datetime value is 3 milliseconds. This is why the ending milliseconds above are shown as 997 instead of 999. Here’s the proof to get the max datetime in case you are interested. To get the minimum, simply add a negative sign to the increments below. [cc lang=”sql”] DECLARE @datetime DATETIME SET @datetime = GETDATE() WHILE 1 = 1 BEGIN BEGIN TRY — increment by days SET @datetime = DATEADD(dd,1,@datetime) END TRY BEGIN CATCH — cannot increment by days anymore WHILE 1 = 1 BEGIN BEGIN TRY — increment by minutes SET @datetime = DATEADD(mi,1,@datetime) END TRY BEGIN CATCH — cannot increment by minutes anymore BEGIN TRY — increment by seconds SET @datetime = DATEADD(s,1,@datetime) END TRY BEGIN CATCH — cannot increment by seconds anymore BEGIN TRY — increment by milliseconds SET @datetime = DATEADD(ms,3,@datetime) END TRY BEGIN CATCH — cannot increment by seconds anymore SELECT @datetime BREAK END CATCH END CATCH END CATCH END BREAK; END CATCH END [/cc] This took 7 seconds to run on my machine, fyi.
Continue reading ...
