Browse by Tags

Blog do Ezequiel
Portuguese PFE SQL Server Team


  • Blog Post: Too many single use plans, now what?

    Hello, I’ve been asked the question in title at least twice in the past month, but the question is really incomplete – I would rather be asked “I have too many single use plans taking up a lot of memory in my server, can I do something about it?”. The answer would be the...
  • Blog Post: SQL Swiss Army Knife #13 – Exploring the plan cache – Part 2

    Hello all, This is long overdue, but here it is, the follow up on plan cache exploration queries part 1 post. There are many "hidden" gems inside a query plan XML that allow us to know our workloads in greater and better detail. Here are a few more xqueries snippets for various purposes. These...
  • Blog Post: SQL Swiss Army Knife #13 – Exploring the plan cache – Part 1

    Hello all, In the last few months I’ve been creating a few snippets of code to extract as much useful information from the plan cache as possible. Why to do this? We at Microsoft GBS PFE deliver a type of service called a SQL Performance Tuning and Optimization Clinic , which is directed...
  • Blog Post: SQL Swiss Army Knife Series is indexed

    Hello all, By popular demand, I’m creating this post to serve as the central location for all past and future posts of the “SQL Swiss Army Knife” series, which should also make it easier to find and new updates. Tag Script Initial Release Latest Update ...
  • Blog Post: About Maintenance Plans – grooming SQL Server

    Hello all, I admit I’m not a big fan of Maintenance Plans (the feature that goes by that name) mainly because of its “do or die” approach. So, it was not by chance that I wrote and keep updating the AdaptiveIndexDefrag procedure. Good maintenance (or lack thereof) may be the difference...
  • Blog Post: SQL Swiss Army Knife #11.1 - Locking, blocking and active transactions

    EDIT (12-04-2013): Added page type information (PFS; GAM or SGAM) when wait type is PAGELATCH_ or PAGEIOLATCH_ . EDIT (23-05-2013): Fixed parse page issue. EDIT (17-10-2013): Added memory grant info, statements to blocking and blocked sections of blocking report, and fixed head blocker info. ...
  • Blog Post: SQL Swiss Army Knife #12 - Index information galore!

    EDIT (06/06/2012): Added script for all databases (may cause performance problems in large servers) and current database only. also fixed issue with partition aligned indexes. EDIT (19/11/2012): Added Heap information and redesigned duplicate and redundant index search. EDIT (18/12/2012): Fixed...
  • Blog Post: The SQL Swiss Army Knife #3 - View I/O per file - Updated

    Hello all, Here is another one focusing on SQL scripts that may help on everyday DBA tasks, following the series "SQL Swiss Army Knife". This script will return, very promptly, the overall I/O statistics for all databases in your server, ordered by stalled I/O, and is based on the sys.dm_io_virtual_file_stats...
  • Blog Post: SQL Swiss Army Knife #10 - VLFs again. What’s your current status?

    EDIT (11-01-2011): A parenthesis was missing making the log_size_MB value skewed. Thanks Luis! EDIT (26-03-2012): Updated script for SQL 2012 support. EDIT (19-11-2012): Added information from Fixing VLFs post , namely potential log size after fix, actual number of VLFs, amount of used VLFs, potential...
  • Blog Post: Adaptive Index Defrag

    Hello all, because the procedure usp_AdaptiveIndexDefrag has undergone a few more updates, starting now this post will be the only source of information for it, including a full change log at the end of this post . It’s also reachable from the links section on the right anywhere on the blog. ...
  • Blog Post: SQL Swiss Army Knife #9 - Fixing VLFs

    EDIT (09-08-2011): missing variable set for sql version. Thanks go to Calvin for finding this bug. EDIT (26-03-2012): Updated script for SQL 2012 support. EDIT (19-09-2012): Simplified logic. EDIT (20-09-2012): Changed grow settings if not SQL Server 2012. Hello all, Here is another post...
  • Blog Post: SQL Swiss Army Knife #8 - Find a Maintenance Window

    Hello all, So this another post on SQL scripts for DBAs, following the series "SQL Swiss Army Knife". This time with a way to “guesstimate” (heard this a while ago) the availability window on a server. Note that this is only usable from SQL Server 2005 onwards. Have you ever needed to...
  • Blog Post: The SQL Swiss Army Knife #5 - Checking Autogrow times

    Hello all, Here is another one focusing on SQL scripts that may help DBAs, following the series "SQL Swiss Army Knife". This time we are exploring an alternative way of verifying autogrow times besides checking the ErrorLog for any recorded information, and that is when an error 5144 or 5145 occurs....
  • Blog Post: The SQL Swiss Army Knife #4 - Making sense of FILESTREAM containers

    Hello all, Here is another one focusing on SQL scripts that may help DBAs, following the series "SQL Swiss Army Knife". This time we are exploring FILESTREAM. Consider the following FILESTREAM enabled database and table in a SQL Server 2008: CREATE DATABASE Archive ON PRIMARY ( NAME = Archive1...
  • Blog Post: SQL Swiss Army Knife #2.1 - Scripting Logins - Updated

    EDIT (20-09-2012) - Added several scripting options; does not use SQL Server 2000 system tables anymore. EDIT (11-01-2013) - Fixed issue with permission syntax depending on version (2005 vs. 2008/2012). EDIT (18-03-2013) - Fixed issue with undeclared variable in cursor; Added options for access...
  • Blog Post: The SQL Swiss Army Knife #1 - Scripting Securables - Updated

    EDIT (11-01-2013): Fixed issue with generating all logins even when single database was chosen. Hello all, I've recently joined the PFE team in Portugal, and one part of the job i like is giving something back to the community. Whenever possible, i will be focusing my posts on SQL scripts that...
  • Blog Post: How to script all my database objects? Here is the answer.

    One question that sometimes arises is "How can we script all database objects throught some kind of automation?" You have several ways to do it. For both SQL 2000 and SQL 2005 you have Microsoft SQL Server Database Publishing Wizard that generates a single SQL script file which can be used to recreate...
Page 1 of 1 (17 items)