Jon Gurgul's Blog

Tag: Script

  • Delta and Cumulative IO Stats

    —

    by

    Jon Gurgul
    in

    Checking IO statistics has always been one of the most important metrics for any DBA. It is no surprise that most people I know will have some version of the following two scripts that use the dmv sys.dm_io_virtual_file_stats (Transact-SQL). The dmv itself is a cumulative view of your IO, therefore in order to get a particular…

  • Time Calculations (Rounding time to 00:00 or x minute)

    —

    by

    Jon Gurgul
    in

    This is a collection of sql code to enable the removal of time from a datetime, or the rounding to the nearest x minute (up or down) The rounding includes 5,6,10,15,20 and 30 minutes time periods, however the scripts are pretty straight forward to edit. The code should run on all versions of sql server,…

  • Simple Table and Index Breakdown

    —

    by

    Jon Gurgul
    in

    This script provides a breakdown with row count and partition information of tables and indexes. It is in essence merely a cut down version of the final Detailed Table and Index Breakdown script. http://gallery.technet.microsoft.com/scriptcenter/Simple-Table-and-Index-61f93894

  • Detailed Table and Index Breakdown

    —

    by

    Jon Gurgul
    in

    When I need to get a detailed overview of my database objects there are a number of scripts that I have written or adapted with varying degrees of depth. Primarily I wrote these scripts as a way to determine rough rowcounts,database and data/index sizes, but as I went I added more and more bits of information.…

  • Investigating CHECKPOINT

    —

    by

    Jon Gurgul
    in

    Investigating CHECKPOINT (Transact-SQL) Writes all dirty pages for the current database to disk. Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk. Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been…

  • Database Created Version or Internal Database Version – dbi_createversion

    —

    by

    Jon Gurgul
    in

    Internal Database Version Sometimes you will see references to a database with a version of 611 or 655 or similar. These numbers refer to the internal database version and have little meaning at first glance, however with a lookup list you can easily tie them to their parent SQL Server version. Here is an example…

  • What is the easiest way to compare data in a SQL Server table and fix any differences?

    —

    by

    Jon Gurgul
    in

    Sync Table Data It is the sort of problem that you often get; you have 2 tables and you want to make sure that the data matches, however there are millions of rows and lots of columns to deal with. Aghhh. Nightmare. So you start writing a script comparing the tables row by row, column…

  • SQL Updates

    —

    by

    Jon Gurgul
    in

    Please see the following resource for a complete list of updates. Where to find information about the latest SQL Server builds https://support.microsoft.com/en-us/help/957826/where-to-find-information-about-the-latest-sql-server-builds  

  • What has changed in SQL 2008 R2 SP1?

    —

    by

    Jon Gurgul
    in

    Changes from SQL 2008 R2 to SQL 2008 R2 SP1 Here is my comparison of changes for the SQL 2008 R2 SP1 update. New Column(s) sys.dm_exec_query_stats http://msdn.microsoft.com/en-us/library/ms189741.aspx sys.dm_os_sys_info http://msdn.microsoft.com/en-us/library/ms175048.aspx New Stored Procedure(s) This procedure raises the number of partitions from 1000 to 15000. sys.sp_db_increased_partitions http://technet.microsoft.com/en-us/library/hh204563.aspx New Views(s) sys.dm_os_windows_info http://technet.microsoft.com/en-us/library/hh204565.aspx sys.dm_server_registry http://technet.microsoft.com/en-us/library/hh204561.aspx sys.dm_server_services http://technet.microsoft.com/en-us/library/hh204542.aspx sys.dm_server_memory_dumps http://technet.microsoft.com/en-us/library/hh204543.aspx…

  • Policy-Based Management Execution Mode

    —

    by

    Jon Gurgul
    in

    Policy-Based Management Execution Mode If you are on a SQL 2008 or later then you are probably aware by now of the ability to set up various policies to help manage/audit, and in some cases prevent or roll back changes. However you may get this far and wonder why the execution mode you want to…