Blog do Ezequiel
SQL Server Insights

 

 


Latest Updates
06/02/2015 - Latest update on the AdaptiveIndexDefrag procedure v1.6.1. Look for it in the "Ezequiel shortcuts" section on the right or just click HERE.

11/03/2015 - Updates to Maintenance plan deployment scripts available HERE

17/05/2015 - The SQL Swiss Army Knife Series Index
  • Blog do Ezequiel

    Adaptive Index Defrag

    • 19 Comments

    The purpose for this procedure to to perform an intelligent defrag on one or more indexes for one or more databases. The 1st release was inspired by an earlier release of Michelle Ufford’s code in SQLFOOL.com site, and has since evolved to suit different and added needs. In a nutshell, this procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, amongst other parameters, like if page locks are allowed or the existence of LOBs.
    All within a specified time frame you choose, defaulting to 8 hours. The defrag priority can also be set, either on size, fragmentation level or index usage (based on range scan count), which is the default.
    It also handles partitioned indexes, optional statistics update (table-wide or only those related to indexes), rebuilding with the original fill factor or index padding and online operations, to name a few options.

    ...
  • Blog do Ezequiel

    About Maintenance Plans – grooming SQL Server

    • 12 Comments
    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....
  • Blog do Ezequiel

    PAGELATCH_EX waits and heavy inserts

    • 9 Comments
    Hello all, Recently I came across an issue over a table that was being inserted into quite intensively, by concurrent operations. The issue, which is not that uncommon, is dealing with PAGELATCH _EX contention, namely when a table has a clustering...
  • Blog do Ezequiel

    Can log files growth affect DML?

    • 8 Comments

    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 or log backup operations. Following that blog post, I became curious as to how having a poor strategy could potentially impact some less than obvious operations within SQL Server and decided to put that to a test..

    ...
  • Blog do Ezequiel

    The SQL Swiss Army Knife #1 - Scripting Securables - Updated

    • 5 Comments

    EDIT (11-01-2013):  Fixed issue with generating all logins even when single database was chosen.
    EDIT (17-05-2015): Fixed duplicate permissions scripted with Procedures/Functions/CLR; Extended object coverage.

    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 may help on everyday DBA tasks, something in the likes of a "SQL Swiss Army Knife".
    According to BOL, SQL securables "are the resources to which the SQL Server Database Engine authorization system regulates access".

    ...
  • Blog do Ezequiel

    The SQL Swiss Army Knife #5 - Checking Autogrow times

    • 5 Comments

    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 do Ezequiel

    SQL Swiss Army Knife #9 - Fixing VLFs

    • 4 Comments
    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...
  • Blog do Ezequiel

    How to integrate SSRS Reports with PerformancePoint Dashboards in SharePoint 2010

    • 4 Comments
    Hello all, Probably a few of you have heard about PerformancePoint in Microsoft’s BI stack. PerformancePoint Services is set of tools and services that allow you to create and publish Business Intelligence Dashboards in SharePoint 2010. PerformancePoint...
  • Blog do Ezequiel

    Best Practices on FILESTREAM implementations

    • 3 Comments
    Hello all, Recently I was asked to advise a customer on a FILESTREAM implementation he was devising, and part of that ended up becoming this blog post that I hope can be of some help for you. When to use FILESTREAM? In SQL Server, BLOBs can be...
  • Blog do Ezequiel

    SQL Server 2012 RC0 Released

    • 3 Comments
    Hello all, Just to share with you a summary with the news, publicly available, about SQL Server 2012 RC:   Technorati Tags: SQL Server 2012 RC0 , What's new   Official Release Information            ...
  • Blog do Ezequiel

    T-SQL Misconceptions - JOIN ON vs. WHERE

    • 3 Comments
    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...
  • Blog do Ezequiel

    SQL Server 2008 R2 – New White Papers

    • 3 Comments
    Hello all, New white-papers were released: SQL Server 2008 R2 Upgrade Technical Reference Guide – Link . SQL Server 2008 R2 Security Best Practices – Link . Cheers,
  • Blog do Ezequiel

    SQL Server 2008 - Cumulative Update 4

    • 3 Comments
    Hi, Yesterday, it was released the Cumulative Update 4, for SQL Server 2008 (RTM), build number 10.00.1798: http://blogs.msdn.com/sqlreleaseservices/archive/2009/03/16/cumulative-update-4-for-sql-server-2008-rtm.aspx This is the KB article link, where...
  • Blog do Ezequiel

    SQL Swiss Army Knife Series is indexed

    • 3 Comments
    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 ...
  • Blog do Ezequiel

    Programmatically find MS Shipped Databases

    • 3 Comments
    Hello, Have you ever needed to programmatically find what databases in you server are part of Microsoft products? Maybe to use that information in your own piece of code? In my case, I needed that information to exclude Sharepoint and Biztalk databases...
  • Blog do Ezequiel

    SQL Swiss Army Knife #11.1 - Locking, blocking and active transactions

    • 3 Comments
    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...
  • Blog do Ezequiel

    SQL Swiss Army Knife #12 - Index information galore!

    • 3 Comments
    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...
  • Blog do Ezequiel

    SQLAzure – “SQL Azure Compatibility Assessment” and “SQL Azure Security Services” Services

    • 3 Comments
      Hi, Recently, SQLAzure Labs released two projects that could help, in different aspects, on the move, of our databases from the On-Premise stack to the SQLAzure world. The first project has the main purpose of helping on this move, performing a...
  • Blog do Ezequiel

    A case of seeks and scans

    • 2 Comments
    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...
  • Blog do Ezequiel

    SQL Server 2012 new high VLFs warning: Important update

    • 2 Comments
    Hello all, If you follow this blog, this is another post on VLFs, a topic I’ve covered several times before. If you want to read more about it just click here . Also, all scripts in the VLF posts have been updated to support SQL server 2012....
  • Blog do Ezequiel

    Too many single use plans, now what?

    • 2 Comments
    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...
  • Blog do Ezequiel

    SQL Swiss Army Knife #13 – Exploring the plan cache – Part 1

    • 2 Comments
    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...
  • Blog do Ezequiel

    SQL Server - Performance Counters, Thresholds, etc.

    • 2 Comments
    These are normal questions: What counters should I select to monitor my SQL Server instance? What thresholds or values should I use to know when something bad is happening? Use this webcast to get answers: TechNet Webcast: SQL...
  • Blog do Ezequiel

    How to script all my database objects? Here is the answer.

    • 2 Comments
    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...
  • Blog do Ezequiel

    SQL Swiss Army Knife #7 - Adaptive Index Defrag

    • 2 Comments
    Hello all, Sorry for the inconvenience, but the content of this post moved to the Adaptive Index Defrag post .
Page 1 of 5 (116 items) 12345