Improving the Performance of the reports by simplifying Drilldown Report to Drill through Report

Recently one of my customers in IT industry, had a P&L drilldown report aggregating the P&L metrics from the company level->client level ->account level -> project level -> employees at the row level while aggregating the data across date hierarchy Calendar Year->Calendar Semester ->Calendar Quarter -> Month at the Column Group Level with Adjacent Column…

|

Unable to run DTS Package fetching data from Oracle using ODBC Driver after migrating to 64 bit

Recently one of my customers migrated and upgraded their servers from SQL 2000 32 bit  to SQL 2005 64 bit following which their DTS packages stopped working. In this blog post I intend to explain the troubleshooting approach, we adopted to resolve the issue along with the resolution to the problem. When you migrate DTS…

|

Gotcha – Listening SQL Server Availability Group Listener on non-default port

While configuring SQL Server AlwaysON Availability Groups, as a part of security hardening practice it is recommended  to configure Availability Group to listen on a non-default port other than 1433. However the moment you configure your Availability Group Listener on a non-default port and if you SQL Server instance is also configured to listen on…

| |

When do we need asymmetric storage with AlwaysON FCI and Availability Storage?

Before we start the discussion on SQL 2012 AlwaysON let me define the terminologies introduced with SQL 2012. As we all know with SQL 2012, we have AlwaysON solution which provides us with a HADR Solution. SQL 2012 AlwaysON can be configured in 2 modes viz AlwaysON      Failover Clustering Instance (FCI):  This is the…

Recovering from cryptic errors thrown when importing from PowerPivot

I am borrowing this title from one of the blog post from Cathy Dumas, since this post is just the extension of her blog post which she posted for resolving the error thrown while creating a SSAS tabular Model project in SSDT by importing from Powerpivot. I started to create a SSAS Tabular Model project…

|

Dependency Map of the Nested Stored Procedures using DMV

One of my customers, had a requirement to identify the call graph (or other words dependency map)  for the nested stored procedures. While with SQL 2008, two new DMVs viz  sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities  to identify the referencing and referenced objects by the stored procedures within the database. However, the DMV gives the information of the…

| |

SQL Server: Does Parallel Plan for rebuild index impact the fragmentation of index?

The title for this post might sound ironical since rebuild of the index is used to remove the fragmentation of the index so how does rebuild of index affect the fragmentation of index? Well, this is what I would like to explain in this blog post. Generally, for rebuilding of indexes (alter index … rebuild)…

|

SQL Server 2012 AlwaysON Automatic Failover Set – Caveat

As I was going through the documentation of AlwaysON, I came across the following caveat with SQL Server AlwaysON Automatic Failover set. While we can have upto synchronous secondary replica,  we can have only one secondary replica to be part of Automatic Failover Set. Now, what that means is only of the synchronous secondary replica…

|

Performance Tuning with Columnstore index using Batch Processing

As we all are aware, SQL 2012 introduced columstore indexes which is based Microsoft’s xvelocity in-memory engine  and as you might have heard and read in numerous articles it is known to provide 5-50X improvement in performance of queries. Reading and Hearing 5-50X improvement in performance of queries causes many eyes to pop-out and they…

Troubleshooting Non-Buffer Latch Contention: LATCH_XX

Latches are lightweight synchronization primitives that are used by the SQL Server engine to guarantee consistency of in-memory structures including; index, data pages and internal structures Latch waits are one of the possible causes of slowness in SQL Server which can limit the throughput (tps) of OLTP application. Whenever we experience slowness in performance of…