Tag: SQL

SQL, SQL 2012

Change Tracking Cleanup Limitation

I’ve been “fortunate” enough to be working a great deal with Change Tracking recently and have run into a limitation that it’s worth keeping an eye out for on your servers.

If you have a database, with Change Tracking enabled, and the total number of transactions exceeds 14.4 million for all of the tables that are tracked in that database then you are going to be in for some hurt.

The problem is that the process to clean up the CT data in the (internal) syscommittab table only runs once a minute, and only cleans up a maximum of 10,000 rows every time it runs.

10000 (rows) x 60 (minutes an hour) x 24 (hours a day) = 14,400,000

Given that it cannot clear faster than that limitation the internal table will continue to grow over time and it will cause other cleanup processes to slow down, as well as your CT queries. This is definitely an issue in SQL 2012, I have no yet checked other versions.

Here’s a quick screen grab where you can see the cleanup and the number of rows cleaned up:

download (7)

 

You can see if you are running into this limitation by checking the age of the earliest syscommittab row and seeing if it exceeds your retention time

select datediff(hour, commit_time, getutcdate()) 
from sys.dm_tran_commit_table 
where commit_ts = (select min(min_valid_version) 
  from sys.change_tracking_tables)

 

You can also check the size and rowcount of the syscommittab table to gauge if it’s something you should be concerned with

selectt.Name as CTName
, sum(p.rows) as ChangeTrackingRows
, convert(numeric(10,2),(sum(u.total_pages)*8)/1024.0) as SizeMB
from sys.internal_tables t
join sys.partitions p on t.object_id = p.object_id
join sys.allocation_units u on (p.hobt_id = u.container_id OR p.partition_id = u.container_id)
left join sys.change_tracking_tables c on t.parent_object_id = c.object_id
where t.name = 'syscommittab'
group by t.name

 

I have a whole bunch more stuff coming on Change Tracking (including an undocumented workaround for this issue), but for now this is important to know.

Indexing, SQL

Filtered Indexes on Computed Columns

I was trying to improve the performance of some code recently and decided that a filtered index could really help me out. Upon attempting to add the index I got an error stating that you cannot add filtered indexes to computed columns. This is the case even if the computed column is persisted.

That’s a shame as this would be a really useful thing to have. 

There is actually an open Connect item on this. It’s been open for 4 years now, so I’m not sure that there will ever be any traction on it, but feel free to upvote it yourself.

 

Code to repeat the problem below:

CREATE TABLE #SomeData 

    (

      RowID INT NOT NULL CONSTRAINT PKSomeData PRIMARY KEY

    , Quantity INT NOT NULL

    , Price MONEY NOT NULL

    , TotalCost AS (Quantity * Price) PERSISTED

    );

 

CREATE INDEX IDX_CostGreaterThan10 ON #SomeData (RowID) WHERE TotalCost > 10;

Msg 10609, Level 16, State 1, Line 1
Filtered index ‘IDX_CostGreaterThan10’ cannot be created on table ‘#SomeData’ because the column ‘TotalCost’ in the filter expression is a computed column. Rewrite the filter expression so that it does not include this column.

 

AlwaysOn, Rant, SQL 2012

You Can’t Meet Your RPO/RTO With AlwaysOn

“That title may have caught your attention. AlwaysOn is the future of HA/DR for SQL Server, and has been since the release of SQL 2012.  

AlwaysOn is actually a marketing term which covers Failover Cluster Instances (FCIs) and Availability Groups (AGs). Allan Hirt (@sqlha | blog) is a strong proponent of ensuring that people understand what this actually means. So much so that he even ranted about it a little. 

I’ve used FCIs for years, going back to the active/passive clustering days of old, and I’ve used Availability Groups in the last few months. They are both great, and both have limitations: FCIs with their shared storage and AGs with some network and quorum oddities. 

Both of them will do a fine job for you if you have the time, patience, and in the case of AGs, money to get them up and running. They still will not allow you to meet your RPO/RTO though. 

Critical to your business and your users is your up time, and that’s where the Recovery Time Objective (RTO) and Recovery Point Objective (RPO)  come into play. They reflect amount of time it will take to get your services back up and running, as well as the level of data loss that you are willing to accept.

 

 

Where FCI/AG win

The key problem with FCI/AG is that they do everything that they can to ensure that transactions are kept as up to date as possible. With FCI you move an entire instance over to another node, everything committed goes with it. With AGs the log records are shipped to the secondaries and applied in a synchronous or asynchronous fashion. The asynchronous setting is designed to get transactions there as soon as possible, and great for longer distances or where low commit times are ultra-critical. Both of these solutions solve two problems…a hardware issue or a software issue.

What does that mean? If your server goes down, then you can failover and lose next to nothing and be back up and running quickly. If Windows goes out to lunch on one of the machines then you can failover and keep ticking along.

So where do they fall down? 

 

 

What FCI/AG cannot do

Let’s say there’s a code release and a table accidentally has an update run against it with no where clause. All of a sudden you have a table in a 500GB database which contains 3 million rows and all of the information is wrong. Your users cannot use the application, your help desk is getting call after call and you are stuck. 

Your only option here is to restore your backup and roll up your transaction logs to the point right before the update happened. You’ve done tests on this and know that it will take 120 minutes to get back to that point. Now you have a 2 hour outage and users are screaming, the CIO is at your desk wondering how this happened, and demanding you get the database back up sooner.

FCIs and AGs are not going to help you in this situation. That update is already committed and so failing over the instance won’t help. The transaction logs were hardened immediately on your synchronous partner and applied within 5 seconds on your asynchronous target.

So how has AlwaysOn helped you in this situation? It hasn’t. And while you can sit there cussing out Microsoft for pushing this solution that has this massive failing it’s not going to solve your problem. That’s why you need something more than AlwaysOn. 

 

 

You can pry Log Shipping from my cold dead hands

 

 “Log Shipping?” I hear you ask, “but that’s so old.”

It sure is. It’s old, it’s clunky, and it is perfect for the scenario I just mentioned.  

You can configure log shipping to delay writing transaction logs to remote servers. Let’s say you delay logs for 1 hour. That accidental mass update was performed, you realize that you are in trouble. You quickly apply the logs on the secondary to the point in time before the update, bring the database online and repoint your clients. You are back up again in 5 minutes. It’s a momentary issue. Sure, you have an outage, but that outage lasts a fraction of the time. Your help desk is not inundated with calls, your users aren’t left out in the cold for hours. 

There’s nothing to say that you have to delay applying those logs for an hour. It could be 2 hours, or even 24. It really all depends on how you want to handle things. 

Sure, you have to do manual failover, and you don’t have the ability for automatic page level restores from one of the synchronous AG secondaries, but you have a level of data resiliency that AlwaysOn does not provide you. 

 

So while AlwaysOn technologies are great, and you should absolutely use them to enhance HA/DR in your business, but you have to be aware of their limitations, and be sure to use other parts of SQL Server to ensure that you can keep your business running. 

SQL

SSMS – Index Outside The Bounds Of An Array

Hit a strange issue this morning. I was working with one of the network guys testing out connecting to SQL behind an F5 load balancer, something I’ve done multiple times previously.

I was able to connect using SQLCMD, Invoke-SQLCmd and using a UDL source, but for some reason, every time I tried to connect using SSMS I would get an error stating that an “Index was outside the bounds of an array”.

SSMS+-+Index+out+of+bounds+on+an+array
Advanced Information for Error

A little research showed that this error cropped up when trying to connect to SQL 2012 from an earlier version of SSMS. This wasn’t the case here though, everything was SQL 2012, and I was able to connect to the server directly without any problems, it was only an error when going through the F5 VIP.

After a little work and research with the network admin we discovered that OneConnect was causing the issue. It was attempting to pool connections and was causing the problem, turning it off fixed the issue entirely.

Just something to keep an eye out for if you use F5’s to handle load balancing or DR client redirection.

SQL

Formatting Number Output

I’ve been working with some large data load processes recently and have been dumping upwards of a billion records into tables. As these are not instantaneous loads I found myself wanting to keep an eye on how much data was loaded.

This is nice and easy to do using the sys.partitions system view. Here’s an example from AdventureWorks

SELECT t.name, rows

FROM sys.partitions p

   INNER JOIN sys.tables t

       ON p.OBJECT_ID = t.OBJECT_ID

WHERE p.index_id IN (0,1)

ORDER BY rows DESC;

top+10

This is all good and well, but when you start getting to large values on the tables it becomes a little difficult to tell the difference between 31 million and 312 million. That’s when I resort back to using commas. Unfortunately SQL Server does not make this easy, although it can be accomplished with a couple of converts.

SELECT t.name, substring(convert(varchar, convert(money, rows), 1), 1, len(convert(varchar, convert(money, rows), 1))-3)

FROM sys.partitions p

   INNER JOIN sys.tables t

       ON p.OBJECT_ID = t.OBJECT_ID

WHERE p.index_id IN (0,1)

ORDER BY rows DESC

top+10+2

This is still a little arduous to type, so I created a function to do the work. Now it’s just a case of calling that function whenever I want to have the numbers presented to me in a much more readable format.

CREATE FUNCTION dbo.fnNumberComma (@iVal bigint)

RETURNS VARCHAR(30)

AS

BEGIN

DECLARE @oVal VARCHAR(30)





SELECT @oVal = SUBSTRING(CONVERT(VARCHAR(30), CONVERT(money, @iVal),1),1,LEN(CONVERT(VARCHAR(30),CONVERT(money,@iVal),1))-3)




RETURN (@oVal)




END




GO












SELECT t.name, dbo.fnNumberComma(rows) as [RowCount]




FROM sys.partitions p




   INNER JOIN sys.tables t




       ON p.OBJECT_ID = t.OBJECT_ID




WHERE p.index_id IN (0,1)




ORDER BY rows DESC;








top+10+3


	


	



			
				


	

	

	

		

						

														
						SQL					
									
							

			
			
Datatypes and xp_fileexist

		

	



	

	
I was using xp_fileexist to perform a couple of file and folder checks and ran across one of the things that you have to be careful of when using undocumented extended stored procedures. 


In this case when I was trying to insert the results into a table variable I was getting an error:





    “Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.”
  




That is particularly unhelpful. You can reproduce this error yourself by running:





DECLARE @Folder NVARCHAR(MAX) = ‘C:Backup’
DECLARE @FolderCheck TABLE
    (
      FileExists BIT ,
      FileIsDir BIT ,
      ParentDirExists BIT
    );


INSERT  INTO @FolderCheck
        ( FileExists ,
          FileIsDir ,
          ParentDirExists
            
        )
        EXEC MASTER.dbo.xp_fileexist @Folder;
SELECT  FileExists ,
        FileIsDir ,
        ParentDirExists
FROM    @FolderCheck;


In a basic troubleshooting effort I tried to run the xp_fileexist without the insert into the table variable and got another error, this one being a little more helpful at first appearance.


DECLARE @BackupDirectory NVARCHAR(MAX) = ‘C:Backup’;
EXEC MASTER.dbo.xp_fileexist @BackupDirectory;





    “Msg 22027, Level 15, State 1, Line 0
Usage:  EXECUTE xp_fileexist  [,  OUTPUT]”
  




I knew that I was passing in the variable and it was valid, this just didn’t make a lot of sense. I decided to change the datatype of the variable in the hopes that it would work:




DECLARE @BackupDirectory NVARCHAR(8000) = ‘c:BackupNewFolder’;
EXEC MASTER.dbo.xp_fileexist @BackupDirectory;





    “Msg 2717, Level 16, State 2, Line 2
The size (8000) given to the parameter ‘@BackupDirectory’ exceeds the maximum allowed (4000).”
  




So, finally an error that gave me actionable information. By changing the length of the variable to a nvarchar(4000) I was able to get the results I was looking for.




This is just one of those examples where you have to be careful what you are playing with and play around to get what you are looking for. Undocumented xp’s can be really useful, but troubleshooting them can also be a nightmare.


	


	



			
				


	

	

	

		

						

														
						Installation, SQL					
									
							

			
			
Issues With Slipstreaming Service Pack 2 on to SQL 2008 R2

		

	



	

	


One of the great things Microsoft introduced a few years ago was the ability to slipstream service packs on to the install media of SQL 2008 and 2008 R2. It was a bit of a painful experience to do, but the results were great as it made deployment of standardized builds a breeze, and when you also added cumulative updates to the mix it became ridiculously easy to ensure every new SQL instance was at the right revision of the product.


Slipstreaming has gone by the wayside with SQL Server 2012. Now, instead of extracting the service pack and doing a bunch of file work you just have to stick the service pack into a folder and add the PCUSOURCE information in your default configuration.ini file.


 


How to slipstream a service pack into SQL 2008 & 2008 R2


The manual process of updating the original source media is not the worst thing in the world, but it’s not intuitive either. Peter Saddow over at Microsoft posted something last year that clearly outline the steps involved to make this happen. You can find the full details on how over at http://blogs.msdn.com/b/petersad/archive/2011/07/13/how-to-slipstream-sql-server-2008-r2-and-a-sql-server-2008-r2-service-pack-1-sp1.aspx


I used those steps for creating slipstreamed versions of SQL 2008 R2 with Service Pack1 and SQL 2008 R2 with Service Pack 2.


 


Testing slipstreamed service pack 2


I’ve been using the slipstream of service pack 1 for quite a while now and have never encountered an issue. When SQL Server 2008 R2 Service Pack 2 came out a little while ago I worked to get it deployed on my preprod and production machines, then decided a couple of weeks ago that it was time to slipstream my installation media so that this would not need to happen for future deployments. We have a lot of QA and test deployments of SQL Server coming up, so I felt it a good use of my time.


I followed Peter’s steps and built myself some new installation media that included service pack 2. Being the good DBA that I am I got a couple of virtual machine spun up so that I could perform some installation testing. I wanted to be sure that all the components were installed at the relevant levels and that my configuration files did not need any tweaking.


The install of just SQL Server 2008 R2 w/SP2 along with replication, full-text search and the client tools went fine. There were no problems with the install and all of the components were at the service pack 2 level when I checked. This being good I moved on to an install that included Reporting Services. This is where I started encountering problems.


 


Installation errors


All of the pre-installation steps went without a hitch, and the components seemed to install without any problems, but then it attempted to start the Reporting Services service, at which point things went sideways.


The install was unable to bring the service online and the installation failed. The SQL Server engine and tools all installed successfully, I was able to access those, but no matter what I could not get Reporting Services to start. I took a while to go through the install logs and found the error:








Parameter 0 : SQL Server 2008 R2@RTM@           
Parameter 2 : Microsoft.SqlServer.Configuration.Sco.Service.StartService            
Parameter 3 : Microsoft.SqlServer.Configuration.Sco.ScoException@1211@1            
Parameter 4 : System.ComponentModel.Win32Exception@-2147467259            
Parameter 5 : SqlRSConfigAction_install_Startup            
Parameter 7 : ReportServer            
Parameter 8 : ReportServer            
Final Parameter Values            
Parameter 0 : SQL Server 2008 R2@RTM@            
Parameter 2 : 0xDC112D1C            
Parameter 3 : 0xD3BEBD98@1211@1            
Parameter 4 : 0xDC80C325            
Parameter 5 : SqlRSConfigAction_install_Startup            
Parameter 7 : 0x22C8A7B3            
Parameter 8 : 0x22C8A7B3








 


Strangely it appears as though Reporting Services is running at the RTM level, and this causes a problem as everything else is at the service pack 2 revision.


I wondered if I had done something wrong, so I tried to slipstream the service pack again, just in case I missed something. I got the same result.


Then I tried putting it over the top of service pack 1 slipstreamed media, but was met with the same result.


No matter what I tried I could not get it to work. Even worse,  I could not apply service pack 2 to the failed Reporting Services installation in an attempt to bring it online. At this point I decided it must be a bug and gave up.


 


Filing a connect item


After multiple attempts with different media on different servers and under different conditions I was no closer to getting Reporting Services installed. Not being able to figure out a way to get it on there (short of deploying media with service pack 1 and then updating it to service pack 2) I decided to use Microsoft Connect to file it as a bug.


Connect ID 771260 is open for this. I fully expect it not to get fixed, as there are only a couple of years left on the support lifecycle, but it’s important that these things are at least brought to the attention of the product team.


 


What you can do


If you slipstream your SQL installs and have encountered this issue please go and upvote the connect item (and say that you can reproduce it). If you slipstream and have not come across this please let me know.