In this post, I discuss 3 methods (of varying complexity) to carry out some disk space monitoring with SQL server. This can be ad-hoc or you can build it into SQL agent jobs to run on a schedule. Now the first thing I’d like to say is this isn’t a replacement for good monitoring software, it’s more of a compliment to monitoring software. It’s a double check in case something goes wrong with your monitoring. Having some handy scripts is useful as well, because it lets you find out info on an ad-hoc basis, rather than relying on alerts or checking manually. [Read more…]
I recently looked at an issue where free space on a backup drive was running low. One option was to increase the storage. Another was to look at the composition of the backups and see if I could try compressing backups with Litespeed for SQL Server.
2 particular backups stood out. Database1 was 93GB and Database2 was 297GB. Incidently, this was only half of the total size as we stripe across 2 drives so the total size was 186GB and 594GB.
Example backup script for Litespeed.
exec master.dbo.xp_backup_database @database = 'Database1', @filename = 'D:\Backup\Database1\Database1_1.BKP', @filename = 'G:\Backup\Database1\Database1_2.BKP', @backupname = Database1 backup', @desc = 'Backup of Database1', @encryptionkey = 'LiteSp1234567', @cryptlevel = 8, @init = 1, @logging = 0, @throttle = 99, @comment = '', @with = 'SKIP', @with = 'STATS = 5', @compressionlevel = 1
For the purposes of this post, the line we are most interested in is @compressionlevel = 1. By increasing @compressionlevel on a scale from one to eight, we can reduce the size of our backups. One thing to note is that the higher the level of compression, the higher the CPU utilisation will be. More highly compressed backups will usually take more time. It is worth testing the backups using incremental increases in @compressionlevel to find the one that best suits your server.
LiteSpeed for SQL Server 7.5 allows a compression level of 1 – 8.
1 = Medium Compression
2 = Medium High Compression
3-6 = High Compression
7-8 = Extreme Compression.
(Previous versions allowed 9-11 but these are now classed as Level 8)
It is worth testing the backups using incremental increase in @compressionlevel to find the one that best suits your server.
In the end, I opted for @compressionLevel = 5 for Database1 and @CompressionLevel = 3 for Database2. The increased backup time to use @compressionlevel=5 for Database1 is justifed, given the 64GB reduction in backup size. For Database 2 backup, there was too large a jump in processing time between levels 3 and 5. If we had no option, we could live with it but the original reduction in size by using @CompressionLevel = 3 was sufficient.
Side note: As a side benefit I wasn’t considering, our backup team advised the tape backup duration reduced by 1.5 hours so there was a positive knock on effect for another team too.
This is a short post which aims to answer the question “How does the SQL Server engine process my select query”?
T-SQL is the dialect of SQL used with SQL Server and is a declarative language. A declarative language takes the user’s request (or query) and works out the best way how to return the data. This differs from an imperative language in which the programmer tells the program how it should process the statement. With this is mind, it should be fairly easy with some simple syntax just to tell SQL Server what we want and get the results back.
An example in English could be “Bring me all my black shoes from the cupboard”. In SQL (and with a bit of stretch of the imagination), this would be written as:
SELECT item FROM dbo.Cupboard WHERE item = 'shoes' AND colour = 'black';
Despite the way we write the query, the SQL Server engine has other ideas about the processing order. The table below shows the order in English versus the order in SQL.
So conversely, if we were to say this in English, it would be something like “From the cupboard, find all the black shoes and bring them to me”.
Why is it done this way?
First, I should point out we will be discussing 6 main clauses (but there are more). They are listed below in logical order with a description. If you read all 6 descriptions, it should be fairly clear why SQL Server processes the select query in this order. e.g. You cannot select something if the place it supposed to be (a table) does not exist. So we go to the table first.
|THE MAIN CLAUSES
(In logical order)
|FROM||The FROM clause is executed first. This also includes JOIN which is a way of combining data from multiple tables into one result set. Thinking about this logically, the table(s) being queried must be identified first or we would have nothing to SELECT from.|
|WHERE||A clause such as
|GROUP BY||You can retrieve a set of all distinct row values based on what is included in the SELECT. e.g.
SELECT Item, Colour FROM [TestDB].[dbo].[Cupboard] GROUP BY Item,ColourThis can also be aggregated. In this example, I show how many times these combinations of values are repeated throughout the table by using COUNT(*).
SELECT Item, Colour, Count(*) AS [Count] FROM [TestDB].[dbo].[Cupboard] GROUP BY Item,Colour
|HAVING||The HAVING clause is similar to WHERE in that it filters the data based on a predicate. The main difference is that it is evaluated AFTER the data has been grouped (and so is evaluated per group) unlike a WHERE clause which you can see is evaluated before a GROUP BY.
If we enhance the example above to include a HAVING clause (say for groups with a count > 1), the syntax would be:
SELECT Item, Colour, COUNT(*) as [Count] FROM [TestDB].[dbo].[Cupboard] GROUP BY Item,Colour HAVING COUNT(*) > 1
|SELECT||Whilst it is written at the beginning of the T-SQL query, the SELECT clause is evaluated near the end. Remember to include a DISTINCT after your SELECT clause if you want to ensure you return no duplicates.
At this stage, you are returning a relational result. You have not guaranteed the order in which you want to see the result set. This means the same query could return the same results in a different order time and time again.
|ORDER BY||The ORDER BY clause allows you to present the data in an order that you deem suitable. e.g.
Notice here that because this is evaluated last and unlike the WHERE clause, you can refer to column Aliases because they have now been created.
This might make me the odd one out but I actually really like replication. It took me a while to get comfortable with it but when I did and when I learned how to troubleshoot transactional replication confidently, I became a fan. Since I exclusively use transactional replication and not snapshot replication or merge replication, this post is only about transactional replication and in particular, how to troubleshoot transactional replication errors.
In the production system I work on, replication is highly reliable and rarely if ever causes the DBA’s headaches. It can be less so in our plethora of dev and qa boxes, probably down to rate of change in these environments with regular refreshes. Due to this, I’ve had to fix it many times. As I explain how I troubleshoot replication errors, I assume you know the basics of how replication works. If you don’t, a really good place to start is books online. It describes how replication uses a publishing metaphor and describes all the component parts in detail.
This is my first non technical blog. Rather, it is about career and personal development.
Why did I want to achieve MCSE Data Platform?
Well, at first I didn’t. It seemed like a million miles away from where I started. My initial goal was just to pass the 70-462 exam (Administering Microsoft SQL Server 2012 Databases) as my boss had suggested it would be a good thing to learn. This was the first I had heard about Microsoft certifications. From there, with each subsequent exam pass, I just extended my goal to MCSA SQL Server 2012 and then MCSE Data Platform.
I had set the target of passing 70-462 by the end of 2013 which would be just over 1 year as a DBA. The great thing about the certification is you can do the 70-461, 70-462 and 70-463 in any order. Since most of my limited knowledge was about Database Administration and the subject I needed to learn most about initially was also Database Administration, this was a no brainer.