Jon Gurgul's Blog

Category: SQL Server

  • SQL Server Instance Security: Scripting Permissions Part 2

    —

    by

    Jon Gurgul
    in

    After writing a powershell way of scripting permissions I thought I better just show the way I used to do it using T-SQL. Although this script is not complete and is kind of relic of my scripting work I thought that I would include it and perhaps maybe if it is on this blog it…

  • Capturing Deadlocks

    —

    by

    Jon Gurgul
    in

    So, deadlocks. “A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock.” http://msdn.microsoft.com/en-us/library/ms178104.aspx How to capture deadlocks? How many ways can you capture a deadlock? Lots! Luckily I do not see a lot of deadlocks in…

  • SQL Server Profiler Trace

    —

    by

    Jon Gurgul
    in

    A Trace can easily be used to monitor what is happening within SQL Server, just start SQL Server Profiler via the Tools Menu in SSMS or via a shortcut. Once started it is a relatively easy process: 1. Connect to the SQL Server you wish to run the trace on. 2. Either use the default…

  • 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…

  • SQL Server Memory: Max Server Memory or Task Manager is wrong

    —

    by

    Jon Gurgul
    in

    Issue 1: max server memory <> max server memory Okay so you have run sp_configure and checked SQL and it has stolen more memory than you told it to. Aghh! sp_configure ‘show advanced options’, 1; GO RECONFIGURE; GO sp_configure ‘max server memory’, 4096; GO RECONFIGURE; GO http://msdn.microsoft.com/en-us/library/ms178067.aspx Why do you fight me SQL Server? Use…

  • Studying for the SQL MCM 2008 (Microsoft Certified Master)

    —

    by

    Jon Gurgul
    in

    This post will list the resources that I have found and that will hopefully give enough material in order to tackle the SQL MCM 2008. It will also provide an update every now and then with my progress. Anyone in the South West  (United Kingdom) preparing for this currently? How easy is it to get a SQL…

  • 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…