THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andrew Kelly

  • SQL 2016 CU2 is now Available

     

    Microsoft just announced that Cumulative Update 2 for SQL Server 2016 is now available for download here. There are a number of fixes including one I have been looking forward to regarding the Query Store. See here for more details on that one. But overall it appears this is an update you should seriously consider if you are running QSL Server 2016.

    Andy

  • SSMS release 16.4 is now available

     

    The newest release of SQL Server Management Studio (SSMS) is now available. The details and download link can be found here. If you haven’t already downloaded or tried the new SSMS I highly recommend getting it and giving it a whirl. With the new release cycles we will see new features and bug fixes on a regular basis. Enjoy.

    Andy

  • Query Store - Forced Doesn’t Always Mean Forced

     

    The new Query Store feature in SQL Server 2016 is a great new addition that we have been anticipating for many years now. There are already a bunch of articles out there that explain what it is even how to go about using it so I won’t repeat that here. However there is one aspect of the feature that has been poorly documented and even a little bit misleading that I want to go over. Please note that as I was writing this blog some of the documentation has been updated by MS and I want to thank them for doing so. The part of this new feature that I want to discuss is the forcing of query plans. After all the ability to force a particular query plan is certainly one of the main reasons this feature exists.  As you read along keep in mind the Query Store tracks at the query or statement level not the overall batch or procedure. Within a particular Query Store (which is database specific) it assigns a unique query_id for each unique statement as defined by the query_hash. We will also have a unique plan_id which is based on the query_plan_hash for a given query_Id. If the query plan for a given query_id changes over time we will get another entry in the Query Store for that combination of the query_id and the new plan_id. This allows us to see the history for a given query for each unique query plan used for such query. The run time statistics such as reads, writes, duration etc. are tied to each combination of the query_id and plan_id. It actually gets a bit more complex in that there are time intervals that come into play as well but for simplicity sake let’s ignore them here, especially since all stats are still tied to the query_id and plan_id combination when aggregated. What this does is allow us to track and see how each plan performed for that query and is very useful information.

    This is after all what allows us to be able to decide which plan we want the query to use. Let’s say we had a situation in which over time we ended up with 2 different query plans due to recompilation and parameter sniffing. Each plan may have been perfect for the parameters passed in on the first execution after each recompile and if called with that same parameter each time things are great. However let’s also assume the 2nd time we got a plan it was based on a value passed into the procedure that was not typical for most executions of this procedure. In fact only .1% of the time will this be a valid plan and thus gives us very bad performance for 99.9% of the subsequent calls to this procedure with other values for parameter passed in. That means most of the time the performance will not be what we want and may cause lots of performance issues.  We can try to recompile the procedure and hope we get the right values passed in to give us the plan that is best for 99.9% of the time but that is somewhat of a gamble. Instead we can use this great new feature called the Query Store and we can force the previous plan that gave us the great performance the majority of the time. We could see from the history of each plan which plan worked best by looking at the statistics kept for each plan.  This would be true even if there were a dozen plans. SSMS even has graphical tools to help us with that decision making and also allows us to force that plan for subsequent runs. We can do this via TSQL as well of course.

    Let’s summarize the situation. We have 2 query plans in the Query Store and the most recent one is also the current plan in the plan cache that is being used for all new executions of this procedure. But that plan is bad for all but .1% of the values we may pass in to the procedure. The previous plan in the Query Store is a much better plan overall and that is the one we want to ensure is used regardless of the value passed in. As such we go ahead and force the plan using the provided tools or TSQL which sets the is_forced_plan to 1 for the 1st plan in sys.query_store_plan. As a simplified explanation this action invokes a recompile and the current plan (which was bad) is replaced with a new plan that is based on the one we forced. That new plan now becomes the current one in the cache and is now the one in the Query Store that all new statistics are tied to as well.

    Most people would think that if they forced a particular plan that was in the Query Store and it was marked as forced we would in fact be using that very same plan identified by the plan_id and query_plan_hash which is tied to the plan we forced. Keep in mind that if there were problems with the recompile such as it was missing an index that was there when the original plan was created we would get an error which would be listed in the force failure columns and a different plan would obviously need to be used. Errors aside most of the time when we force a plan it gets recompiled and we end up with the same plan as that which we forced. If that plan is the same as the original one we forced it will have the same query_plan_hash and thus the same plan_id.  All future executions will now use that plan and all statistics will be tied to it as well.  This is exactly what we would expect once we forced a plan in the Query Store.

    OK so here is where things may not happen exactly as you would expect. When you force a plan as stated earlier it goes thru a recompilation process and it tries to generate the same or very similar plan as the one you picked. Again assuming nothing has changed such as objects being created or dropped most people would assume it would give us the exact plan we forced. However it is not guaranteed that you will get the same exact plan and in fact it may have a different plan_id and query_plan_hash altogether. How can it do that you say, after all didn’t I FORCE that plan? Yes you did but during the recompile process it may have found a slightly different (maybe better maybe not) way to accomplish the same task that is very similar yet somewhat different than the original plan. For instance the original plan was a Clustered Index scan as is the new one, but the new one may have added, moved or removed an operator which still gives it a valid plan just not the exact one you forced. In this case since it gets a new plan_id all subsequent statistics will be tied to the new plan_id and not the one you actually forced. The is_forced_plan is still set to 1 for the original plan that you forced where as the one currently being used is set to 0.  That part can be a bit confusing to some people unless they understand how this came to be.

    I realize this has been a very long winded explanation but in my opinion necessary to ensure everyone fully understands this potential anomaly so that when or if they see it they will know it is by design and not a bug. It is important to keep in mind that the new plan even if different will be similar to the original plan (the one you forced). As such it will likely perform just fine and get the intended job done which was to get the procedure using a plan that was more appropriate for the majority of the calls. The differences in the plan from the actual forced plan may or may not be noticeable from an overall performance standpoint and as always it depends. But if you are tracking the statistics in the Query Store it is important to know that even if there are no warnings of a failed forced plan the currently active plan may have a different plan_id than the one you manually forced.  One other thing to note is that if the current query plan is recompiled it will always go back to the plan that is marked as forced and that will be the one used in the recompile process. So it may very well pick the original plan again and now you are back to operating as you expected to begin with.

    Don’t in any way take this post to be a warning not to use the Query Store because it is not. This is simply an attempt to make people aware of the potential behavior that is not well documented or can be confusing to many of us. I too was confused until I dug deep enough to figure out what was going on and hopefully this will save you the time and effort to do the same.  Here is an example of how you can see this behavior and why it happens. I think it will go a long way towards making people aware of this behavior and understanding why it is normal albeit a bit unexpected.

     This example uses the new WideWorldImporters sample database for SQL Server 2016 with just a few slight modifications. First I update a single row in the Sales.Invoices table to set the DeliveryMedthodID = 1. All other rows have a DeliveryMethodID = 3. I then create a stored procedure as shown below which queries the Invoices table using the DeliveryMethodID tied to the input parameter of the procedure.

     

    USE WideWorldImporters ;

    GO

    -- This allows for a NCI Seek since thre is only 1 row with this value

    UPDATE Sales.Invoices SET DeliveryMethodID = 1 WHERE InvoiceID = 425 ;

    GO

     

     

    IF OBJECT_ID('Sales.GetInvoicesByDeliveryMethodID',N'P') IS NOT NULL

        DROP PROCEDURE Sales.GetInvoicesByDeliveryMethodID

    GO

    CREATE PROCEDURE Sales.GetInvoicesByDeliveryMethodID

    @DeliveryMethodID INT

     

    AS

    BEGIN

     

        SET NOCOUNT ON ;

     

        SELECT TOP (10000) * FROM [WideWorldImporters].[Sales].[Invoices] AS i

            WHERE i.DeliveryMethodID = @DeliveryMethodID ;

     

    END ;

    GO

     

    I then clear out the query store tables using this command:

    ALTER DATABASE [WideWorldImporters] SET QUERY_STORE CLEAR ;

    GO

     

    Throughout the demo you can use this query to see the results in the Query Store stables.

    SELECT--CAST(p.query_plan AS XML) AS [XML Plan],

           q.query_id, q.query_hash, p.plan_id, p.query_plan_hash

         , p.is_forced_plan, last_force_failure_reason_desc AS [Failures]

         , SUM(s.count_executions) AS [Execs], SUM(s.avg_logical_io_reads) AS [Avg LReads]

         , MAX(s.last_execution_time) AS [Last Execution], MAX(q.last_compile_start_time) AS [Last Compile], t.query_sql_text

    FROM sys.query_store_query AS q

    JOIN sys.query_store_query_text AS t ON q.query_text_id = t.query_text_id

    JOIN sys.query_store_plan AS p ON q.query_id = p.query_id

    JOIN sys.query_store_runtime_stats AS s ON p.plan_id = s.plan_id

    WHERE t.query_sql_text LIKE '%(@DeliveryMethodID int)SELECT%' AND t.query_sql_text NOT LIKE 'SELECT%'

    GROUP BY q.query_id, q.query_hash, p.plan_id, p.query_plan_hash, p.is_forced_plan, last_force_failure_reason_desc, t.query_sql_text

    ORDER BY q.query_id, p.plan_id ;

    I then will execute a series of commands as shown directly below which will show the behavior outlined in the blog above. Just below this code is a step by step description of what happens and why, including the query plans that result from each stage.

    EXEC Sales.GetInvoicesByDeliveryMethodID @DeliveryMethodID = 3 ;

    GO

    EXEC Sales.GetInvoicesByDeliveryMethodID @DeliveryMethodID = 1 ;

    GO

     

    EXEC sp_recompile N'Sales.GetInvoicesByDeliveryMethodID' ;

    GO

     

    EXEC Sales.GetInvoicesByDeliveryMethodID @DeliveryMethodID = 1 ;

    GO

    EXEC Sales.GetInvoicesByDeliveryMethodID @DeliveryMethodID = 3 ;

    GO

     

    -- Force the scan plan  (query_id, Plan_id)

    EXEC sys.sp_query_store_force_plan 1,1;

    GO

     

    EXEC Sales.GetInvoicesByDeliveryMethodID @DeliveryMethodID = 1 ;

    GO

     

    First we execute the stored procedure twice ensuring we pass in the value = 3 for the first execution. Since all but 1 row in the table will math that value we will get a plan that is a full scan of the Clustered Index on the Invoices table as shown below, which includes a filter operator after the scan to filter the WHERE clause SARG. When you run the supplied query you will see that we have 2 executions for the same combination of query_id and plan_id in the Query Store tables.

    Plan1

    Next we recompile the stored procedure to simulate a situation in which the current plan becomes invalid such as statistics being updated on the underlying table or index.  

    Then we execute the procedure two more times careful to pass in the value = 1 for the parameter the first time after the recompile. This simulates a situation in which we now have a query plan that was based on an atypical parameter and as mentioned in the blog above will give us poor performance for any future executions with a value other than 1.  The reason is that the new plan was based on a single row being returned vs. 10K rows and was able to utilize the nonclustered index on the DeliveryMethodID as shown below.

    Plan2

    While the index seek may be great for a single row it is far more expensive than a scan when retrieving 10K rows. This can be seen by looking at the Avg LReads column which shows the new plan over twice the reads as the original plan.

    Once we realize this we quickly decide to force the original query plan by using the system supplied stored procedure. Please note that the two parameters used in the example for the forcing may not match the ones in your environment.  If not please change them accordingly. The first parameter is for the query_id and the 2nd is for the plan_id that you wish future executions use.

    Finally we then execute the procedure once more using the parameter value = 1 and then take another look at the Query Store results.  We now see that there is a 3rd plan that was generated for that query_id and it has a different plan_id and query_plan_hash than the one we forced.  We see that the is_forced_plan column is set for the one we forced and not the new one that was generated as a result. However you can see that the statistics for any new executions are tied to the newly generated plan and not the one marked as forced. Again this does not mean it is broken or that there is a bug it is simply a different implementation then most people would have expected when first using the Query Store and especially if they read the documentation as it existed before a few days ago.

    I can see that you have one last question though and that is “why did it generate a new plan when it should have used the original one since the original plan is still valid”? That is a good question and luckily I have a good answerJ. If we look at the plan that was generated below we will see a slight difference in the overall plan. Notice that we no longer have the filter operator highlighted in the 1st plan. Instead if we look at the properties for the scan operator we see that the optimize chose to push the predicate (WHERE DeliveryMethodID = @DeliveryMethodID) as part of the scan operation itself. If you remember the original plan sniffed the parameter passed in on the first execution and it was a 3 which matched all rows but one. The optimizer knew that all rows but 1 matched this value and we had a TOP 10K on a table with much more rows than that. So it made sense that it could simply push ~10K rows down and filter later. However when we forced the plan which caused a recompile the very next time we executed the procedure we passed in a 1 which only has 1 matching row. Normally that would use the nonclustered index and do a seek. But since the original plan that we forced was a clustered index scan this needed to also do a nonclustered index scan. But this time the optimizer saw that the value we were looking for only matched one row and decided that it could filter on that row during the scan better than filtering later in the process. Or it simply found this plan was close enough to the original and stopped there. I honestly don’t know which but suspect the 1st explanation is more correct. 

    Plan3

    In either case the point is that we can indeed end up with different plans than what we have explicitly forced using the Query Store. Most of the time this will likely never be a problem but there are always exceptions and you may find some day the plan it generated is not what you ideally want and this is why. Also be aware that a recompile can happened for many reasons at any time so exactly which plan it will use going forward may change over time. Chances are whichever plan it choses will still be better than the one that caused you to force one in the first place if you did your homework correctly.  Another question I hear is “why not just use a query hint such as OPTIMIZE FOR in the procedure and be done with it”? Well one good reason is that you may be using 3rd party software that uses procedures in which you are not allowed to change. This in my opinion is a much better and easier solution than using plan guides.  And one last tip is that once you do force a plan you should check it regularly to ensure it is still doing what you expect. Your fellow DBA may decide to make changes to the objects this plan references and make your forced plan invalid without you knowing. The Query store is just another tool in our SQL Server toolbox that certainly has the potential to give us great benefit but if misused or neglected can have the opposite effect.

    If you are going to the PASS Summit 2016 and Query Store sounds interesting to you and you want to know more please attend my session for a better overall summary of what it is and how to use it.

    If you made it this far thanks for hanging in there and good luck,

    Andy

  • SQLSentry does it Again – Plan Explorer is Completely Free

     

    Many of us have used the free version of Plan Explorer from SQLSentry for a long time to help tune and explore query plans in a way that SSMS can only dream of. Unlike most free tools this one still had plenty of useful features that served the community well. The Pro version did however contain a bunch of great features that many users wanted but sometimes couldn’t get the internal approval to purchase a license. Today that all changes as SQLSentry announced that there is now only one SKU which includes ALL the features from both of the original SKU’s and it is completely free.  This also includes the two new features for Performance Profiling and Index Analysis. I honestly don’t get paid a penny to say any of this stuff I simply believe 110% in their products. I have been telling people about Plan explorer for a long time because I know how much more efficient a DBA / Developers life can be using it over SSMS related to plan tuning and analysis. I use it daily and I guarantee that you will agree once you have tried it.  There is no need to register and you don’t even have to give your email which is something else most tool vendors insist on and discourages some people from even trying their tool. Here is a link to Greg’s blog post which goes into even more details about this fantastic news and opportunity for all of us.

    Plan Explorer is now Free

    You can also attend a free query tuning seminar with Aaron Bertrand on Friday September 9th between 11:00AM and noon eastern time where he will show you how to use the new tool more effectively.

    Free Seminar on Query Tuning

    Does it get any better than this? A free tool that will make your SQL Server life more efficient and enjoyable and free training as well.  Do yourself a favor and take advantage of this opportunity and I promise you won’t regret it.

     Andy 

  • Revised File & Wait Statistics Procedures



        For many years I have been using a set of stored procedures to capture and report on both the file and wait statistics inside of SQL Server and I just recently did some more modifications that I wanted to make available. Since many of you have received copies of the past revisions via conferences or articles and such I would recommend taking a look at the newly modified ones as well. These are still the same basic procedures as the previous ones (see here) but I have added some additional filters for the Wait Stats to account for SQL2014 and 2016. I also added another optional parameter to make use of the FORMAT() function available in SQL2012 and up. And finally I dropped the year in the name as these should work for SQL2008 and greater releases (minus the formatting for < 2012). So the older versions will work fine since those newer wait types just won’t appear in the first place.

     

    Gather procedure changes

    1. Changed the names to remove the SQL version years.
    2. Added a Clustered Index on the Capture Time column.

     

     Reporting procedure changes

    1. Changed the names to remove the SQL version years
    2. Added an optional parameter to allow for formatting the results showing commas, decimals and %.

     

    The overall use of the stored procedures has not changed and is very simple overall. They can be used in an adhoc fashion or in conjunction with a SQL Agent job to periodically collect the statistics.  Once you have 2 or more snapshots in the tables you can use the report stored procedures to display the data via whatever time frame and style you wish. I have even included a couple of spreadsheet templates for logging the report results which do a different job of formatting the numbers than the report procedures themselves. We all know there are plenty of ways and available scripts to report on the file or wait stats and I am in no way trying to push the use of these. This is mostly to give everyone who already uses them my updates and to make them available for anyone else who wishes to use them as well. 

    Here are some quick examples on how to call them. All parameters are optional and work just like any other TSQL stored procedure does with parameters. 

    --  Gather Stats

     

    By default the gather procedures will simply take a snapshot and insert the results into the table of whatever database you decide to create these procedures in. If you specify a 1 as the parameter it will first truncate the table and then take a new snapshot.

    EXECUTE [dbo].[gather_wait_stats] @Clear

    EXECUTE [dbo].[gather_file_stats] @Clear

     

    --   Report stats

    Both the reporting procedures have an optional Begin and End DateTime parameter to designate a particular time frame to report on. It will find the closest snap shot to the DateTime you specify if it is not exactly the value you passed in. Both have a new parameter that will format the results as strings with commas, percent signs etc. This uses the FORMAT() function new to SQL2012 and can be customized to a specific country code.  One thing to point out is that while the formatting makes for an easier report to read directly in the SSMS results window it gets left justified due to the output being converted to a string vs. numeric as without the formatting. The next optional parameter for both is the number of rows to limit the final report results to. And finally the file stats procedure has an optional database ID parameter.  If you don’t specify a Begin and End DateTime it will default to the very first and last snapshot in the table. The below examples show the optional parameters for each.

    EXECUTE [dbo].[report_wait_stats] @BeginTime, @EndTime, @TopNN, @Formatted ;

    EXECUTE [dbo].[report_file_stats] @BeginTime, @EndTime, @TopNN, @DBID, @Formatted ;

     

    I typically place the gather procedures in a SQL Agent job that runs every 4 hours by default to insert a snapshot of both the file and wait stats into the history tables. From there you can always insert new snapshots at any time manually if needed. The reports will allow you to query at whatever snapshot intervals you want based on what is in the table at that time. This makes it useful to periodically see how your system is doing at regular intervals in a very efficient way.

    The files are zipped up and attached to this blog post so have fun and I hope some of you find this useful. Feel free to modify to suite your needs. These are after all intended to be a useful tool not just a hammerJ.

     

    Andrew J. Kelly

  • Free or Useful Windows and Developer Resources

     I was just made aware of a bunch of resources that you may find very useful. I especially liked the Windows 10 jumpstart videos but there are a lot of good resources here for free. Have fun everyone.

     

    Opportunity Links Remarks
    Developer Tools Download https://www.visualstudio.com/?Wt.mc_id=DX_MVP5438 Download any Visual Studio skus, including VS 2015
    Developer for Windows 10  -What’s New https://dev.windows.com/en-us/getstarted/whats-new-windows-10/?Wt.mc_ic=dx_MVP5438 All the info developers may need to write apps
    Get Started https://dev.windows.com/en-us/getstarted/?Wt.mc_ic=dx_MVP5438
    Design https://dev.windows.com/en-us/design/?Wt.mc_ic=dx_MVP5438
    Develop https://dev.windows.com/en-us/develop/?Wt.mc_ic=dx_MVP5438
    Publish https://dev.windows.com/en-us/publish/?Wt.mc_ic=dx_MVP5438
    Windows 10 courses in MVA https://www.microsoftvirtualacademy.com/en-US/training-courses/getting-started-with-windows-10-for-it-professionals-10629/?Wt.mc_ic=dx_MVP5438 Great site to get online courses on Windows 10
    Channel https://channel9.msdn.com/windows/?Wt.mc_ic=dx_MVP5438 Another great online resource for Windows 10 related videos
    Windows 10 Jumpstart http://www.microsoftvirtualacademy.com/liveevents/preparing-your-enterprise-for-windows-10-as-a-service/?Wt.mc_ic=dx_MVP5438 A live event on August 14th

     


  • Updated File & Wait Statistics Procedures

        

        


    Please note that I have a newer version of these procedures that you can find here

    http://sqlblog.com/blogs/andrew_kelly/archive/2015/09/02/revised-file-wait-statistics-procedures.aspx 

    .  For many years I have been using a set of stored procedures to capture and report on both the file and wait statistics inside of SQL Server and I just recently did some minor modifications that I want others to be aware of. Since many of you have received copies of the past revisions via conferences or articles and such I would recommend taking a look at the newly modified ones as well. Most of the changes come in the form of optional parameters but three of them may not be drop in compatible and I will explain each of them below. BTW these procedures are all labeled 2012 but I believe they will work just fine in 2008 or even 2005. The only reason I even had a different procedure for each version of SQL Server was due to additional wait types that were added over the years that I chose to filter out of the results. So the older versions will work fine since those newer wait types just won’t appear in the first place.


    Gather procedure changes

    1. Swapped the begin and end time parameters in the file stats proc to be consistent with the wait stats one. I don’t know why it was ever different but both now use @BeginTime first and @EndTime as the second parameter.

    2. Removed the ability to reset the actual SQL Server Wait Statistics counters when specifying the optional @Clear parameter. Now both procedures simply truncate the tables before taking a fresh snapshot.

     Reporting procedure changes

    1. Fixed a bug in both procedures that did not always select the closest snapshot when specifying either the @BeginTime or @EndTime parameters.

    2. Removed the optional parameter to filter out OLEDB waits from the wait stats report procedure.

    3. Added the ability to return only the TOP nn rows via an optional parameter called @TopNN.

      1. For file stats the results are first sorted by total IOStallMs DESC to produce the TOP nn rows.  If @DBID parameter is specified then only the Top nn rows for that database are returned.

      2. For wait stats the results are first sorted by total wait time DESC to produce the TOP nn rows.

    4. Added the ability to specify a database ID via the optional parameter @DBID. This only returns rows for that database in the report.

     

    The overall use of the stored procedures has not changed and is very simple overall. They can be used in an adhoc fashion or in conjunction with a SQL Agent job to periodically collect the statistics.  Once you have 2 or more snapshots in the tables you can use the report stored procedures to display the data via whatever time frame and style you wish. I have even included a couple of spreadsheet templates for logging the report results which do a better job of formatting the numbers than the report procedures themselves. We all know there are plenty of ways and available scripts to report on the file or wait stats and I am in no way trying to push the use of these. This is mostly to give everyone who already uses them my updates and to make them available for anyone else who wishes to use them as well.  

    Here are some quick examples on how to call them. All parameters are optional and work just like any other TSQL stored procedure does with parameters.  By default the gather procedures will simply take another snapshot and insert the results into the table of whatever database you decide to create these procedures in. If you specify a 1 as the parameter it will Truncate the table and then take a new snapshot.

    --  Gather Stats

    EXEC dbo.gather_wait_stats_2012

    EXEC dbo.gather_file_stats_2012

    Both the reporting procedures have an optional Begin and End DateTime parameter to designate a particular time frame to report on. The next optional parameter for both is the number of rows to limit the final report results to. And finally the file stats procedure has an optional database ID parameter.  So the first 2 examples below will give a report for everything of the delta of the very first and very last snapshots in the tables.

    --   Report stats

    EXEC dbo.report_wait_stats_2012

    EXEC dbo.report_file_stats_2012

     These show how you can specify a Date or a Datetime along with a TOP 10 limit and in the case of the File Stats only show tempdb’s data.

    EXEC dbo.report_wait_stats_2012 '20140206 15:51:44.127', '20140208', 10

    EXEC dbo.report_file_stats_2012 '20140206 15:51:44.127', '20140208', 10, 2

     

    The files are zipped up and attached to this blog post so have fun and I hope some of you find this useful,

     

    Andrew J. Kelly

     



  • sp_spaceused Alternative

     

          I don’t know why but for some reason I have never liked using sp_spaceused. It probably started a long time ago when the results were often inaccurate due to the meta data being out of date. I am pretty sure that was fixed somewhere along the line but the system stored procedure had some other limitations that usually prevented me from seeing the data that I really wanted in most cases. So at some point I created my own stored procedure to view the data I was most interested in almost on a daily occurrence. Now to be totally accurate I started with code that one of the SSMS reports uses to show data and index space usage.  The code was not the prettiest so I did clean it up a little and modified it to better suite my needs but the core worked so I used it. I then added some additional queries to get other data that the original query didn’t have such as the file size and growth info. I also slightly modified one of the many variations of functions available out there that format numbers with commas for better readability. In this case it is simply called dbo.commas. Normally I have a database on every instance in which I keep custom procedures / functions etc. in which I can count on certain objects to exist so I can reference them in other objects. These examples are shown with no fully qualified path to the dbo.commas UDF so the UDF and the procedure would have to exist in each database or the one that you care to use it in. While that works you should consider doing one of two alternatives. Either add a utility database like I mentioned and put the two objects there or rename the procedure to have sp_xxx and put the objects in the master database and then mark them as system objects.  That way you can call the procedure from the context of any database.

    OK so enough about that lets see what the procedure does and how to use it. The procedure has two parameters @DBName and @ShowFileInfo. The @DBName parameter is pretty self explanatory and will dictate which database the results are for. The second parameter is really optional as it defaults to a value = 0.  If the @ShowFileInfo parameter = 0 then only one result set will be returned which contains the relevant data for each table in the database. More on that in a bit. If a value of 1 is passed in then a 2nd result set is returned that contains information related to the size of the database, it’s files and growth. See further down for more details on both result sets.  These are all acceptable ways to call the stored procedure:

    EXEC dbo.ListDBSpaceInfo @DBName = 'Adventureworks'
    EXEC dbo.ListDBSpaceInfo @DBName = 'Adventureworks', @ShowFileInfo = 0
    EXEC dbo.ListDBSpaceInfo @DBName = 'Adventureworks', @ShowFileInfo = 1
    EXEC dbo.ListDBSpaceInfo 'Adventureworks'
    EXEC dbo.ListDBSpaceInfo 'Adventureworks', 1

    Here is what the results would look like if you ran it against the Adventureworks database.

    ADWorks

    The first result set lists each table showing the size in MB’s for the reserved, data and index space. The row counts and the Created and Modified dates. This is the information I find most useful the majority of the time. It orders the tables by Reserved MB but you may prefer by table name or something else. The code is easy enough to modify to sort however you like and you can even add an additional parameter to chose the sort by column.  Again I use it this way most often so it is what it is.

    The 2nd result set if you choose to return it will list certain information about the database and it’s files. It will show the current total size of all the data files which is pretty definitive by nature. However the next few columns are dependent on the max size and growth properties of each file. For instance if any of the data files are set to –1 which means unlimited growth the resultant column for Max Size will say Unlimited as will the Potential Free Space. If the files have a fixed size the total max size if then calculated and displayed along with that amount minus the current data usage to give you the potential free space. Basically that is how much free space the database can theoretically accommodate assuming there is enough space on the disks. The same holds true for the log file. These numbers are most useful if you have limits set but the information can give you a good idea of the overall condition of the data and log file usage.

    Now I understand that none of this is information that wasn’t available to users before but I usually had to go to several different sources to get a comprehensive view like this. The purpose of this blog post was not to wow everyone with some rocket science code it was simply to share with others who may find it useful. I use it all the time so I figure there must be others who can take advantage of it as well and hopefully customize it to suite their needs like I have. Have fun everyone, here is the code:

    Andy

  • Speaking at University of Virginia

    The University of Virginia is having a fall conference that is free and open to the public on Tuesday November 13th. My good friend Andy Leonard and I will be speaking on various SQL Server related topics so if you are in the area and available that day you are most welcome to stop by. You can find more information and register for the conference here.

    Andrew J. Kelly

  • Backup File Naming Convention

    I have been asked this many times before and again just recently so I figured why not blog about it. None of this information outlined here is rocket science or even new but it is an area that I don’t think people put enough thought into before implementing.  Sure everyone choses some format but it often doesn’t go far enough in my opinion to get the most bang for the buck. This is the format I prefer to use:

    ServerName_InstanceName_BackupType_DBName_DateTimeStamp_nn.xxx

    ServerName_InstanceName = Pretty self explanatory but lets look at it. Let’s say that the machine name is M432 and the instance is Dev2008. That would normally be W432\Dev2008 however I don’t like special characters so I change it to W432_Dev2008.  If it was a default instance it would be W432_W432. Some people (including myself) prefer to leave off the Server Name if it is a default instance but that is up to you. Since the default instance is always the name of the server it’s still pretty clear where it came from.

    BackupType = FULL, DIFF or LOG.  Nothing more is needed here.

    DBName = Full name of the database. One note here is that if the name has spaces I like to replace the space with some other valid character and some people prefer to remove the space altogether. Spaces in an object name is a whole debate in itself and I wont go there now Smile.

    DateTimeStamp = yyyymmddhhmmss. This allows me to know exactly when the backup started just by looking at the name and makes it unique as well. I don’t know any one who takes two backups of the same db in less than a second so this convention works to avoid file name conflicts.

    _nn = The individual number associated with backing up to multiple files for a single backup operation. Typically backing up to multiple files for a FULL or DIFF backup can be more effecient with larger dbs so appending a number from 01 to nn ensures uniqueness as the rest of the name will be the same. If it is a single file then you can simply use 01 or omit that part altogether.

    .xxx = I also like to use the extension to identify the tool used to create the backup file. For native SQL Server backups I use .bak regardless of the type of backup (Log, Diff or Full). For backups done using a 3rd party utility such as the one from Red-Gate I would use .sqb and so on.

    This naming convention allows me to do several things. First it ensures each backup file will have a unique name. Second it allows me with a quick glance to see where the backup originated, what type of backup it is, which database it is for, when the backup started and which tool was used to create the backup.  So again there is nothing particularly new to this approach but I often see the naming falling short of this and generally only having the DB name and timestamp. Why not take the extra few steps to ensure you get the most out of your naming that you can. The code to generate the whole file name is pretty simple and can be done dynamically so why not go this route? OK there are always exceptions so let’s not start a debate war Smile.

    Have fun,

    Andy

  • Speaking at SQL Saturday #146

     

    For any of you up in the New England area that are looking for some good and free SQL Server training you may want to check out the SQL Saturday this fall in southern NH. More specifically the event will be held in Nashua NH on October 20th 2012. There is a wonderful cast of speakers including myself (shameless plug Smile ) with a wide range of topics of which I am sure everyone can find a few topics they are interested in.  I hope to see some familiar faces from my old stomping ground and a few new ones as well. See you all there.

    Andy

  • Speaking in Raleigh NC 9-20-2011

     

    If you are in the area I will be speaking at the Triangle SQL Server Users Group meeting on Tuesday the 20th of September 2011. The session is on TempDB Best Practices and all of the details for the topic, directions etc. can be found here.  Be sure to register so they have enough food.

    Thanks and hope to see you all there,

    Andy

  • Speaking at the Charlotte Users Group

     

    I will be giving a presentation in between Earthquakes and Hurricanes in Charlotte NC. this Wednesday the 31st of August on Understanding Indexes in SQL Server 2008. If you are in the area please drop by and say hello. You can find out more information and register for the event here.

    Andy

  • Finding IP Addresses With CMS

     

    I was just tasked with putting together a list of all the IP Addresses of the SQL Servers that one of my clients deals with on a daily basis. In this case there was over 30 of them and while I could ping each one individually I found an easier way that you might find useful as well if you are in the same circumstance. I figured there must be a column in one of the DMV’s that shows the IP Address and sure enough the first DMV I looked at had just what I was after. The local_net_address column of the sys.dm_exec_connections DMV shows the following information as taken from BooksOnLine:

    Represents the IP address on the server that this connection targeted. Available only for connections using the TCP transport provider. Is nullable.

    So chances are on a server with several connections you will get what you are looking for with the following query:

    SELECT DISTINCT local_net_address
        FROM sys.dm_exec_connections
            WHERE local_net_address IS NOT NULL ;

    Now this doesn’t do me much better than pinging each server unless I have a way to query them all at once. That is where the under utilized feature of SQL 2008 comes into play called the Central Management Servers.  I won’t bore you with my own explanation of that feature as it is well documented already. Here is one place to look.  But suffice to say that with this feature I can register all my servers and run a single query against them all at once. I then copied the results and pasted them into a spreadsheet and there you go. This is a pretty simple concept but I know people look for this enough that I thought I would share it.

     

    Andy

  • Speaking in Richmond VA

     

    I meant to blog about this many days ago but such is life right Smile.  In a few hours I will jump into my truck (wish it was the Harley) and drive up to Richmond Va. to see my good friend Andy Leonard and the other folks at the Richmond SQL Server Users Group. I will be speaking on the topic of Understanding Indexes In SQL Server 2008 and hope to see a bunch of you there. Directions and more details are listed on the web site.

    Andy

     

    SolidQ Home: http://www.solidq.com/gl-en                       SolidQ Blog: http://blogs.solidq.com

    SolidQ Courses: http://www.solidq.com/squ                     The SolidQ Journal: http://www.solidq.com/sqj

More Posts Next page »

This Blog

Syndication

Privacy Statement