Browse by Tags

Blog do Ezequiel
Portuguese PFE SQL Server Team


  • 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: Table variables and row estimations – Part 2

    Hello again, On my previous post , we covered how the Query Optimizer handles row estimation when using Table variables under specific conditions. Following up on that, I will demonstrate other scenarios where the Query Optimizer must try to optimize queries when no statistics and histograms are...
  • Blog Post: Table variables and row estimations – Part 1

    Hello all, The subject of estimated rows vs. actual rows in plan execution has a lot of impact in query performance, and the source of these skews can be quite diverse, from outdated statistics, to incorrect sampling, or the inability of the query processor to know the value of certain variables at...
  • 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: 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: A case of seeks and scans

    Hello all, A couple weeks ago while I was onsite, I was looking at some queries that had performance issues. To the customer, one in particular had become a conundrum that could not be easily answered with the usual approach of good indexing, simply because there was already a good covering index...
  • Blog Post: T-SQL Misconceptions - JOIN ON vs. WHERE

    Hello all, Last week while I was onsite, I was discussing with a customer about some T-SQL querying misconceptions, one particularly caught his interest. In the subject of JOINs , I was asked if there are there any known issues using search arguments in the ON predicate instead of a WHERE predicate...
  • 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: 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: 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: Can log files growth affect DML?

    Hello all, A while back I blogged here about how a good strategy of log file growth could potentially impact ongoing operations with your SQL Server. It’s known that VLFs (number and size) impact on the performance of such actions as scanning all VLFs for transactions that are marked for replication...
  • 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...
Page 1 of 1 (15 items)