|

Smart Transaction log backup, monitoring and diagnostics with SQL Server 2017

Smart Transaction Log Backups Most DBAs today operating their SQL Server database in full recovery model, schedule their transaction log backups to recur on a specific time interval (every 15 mins) for a highly transactional database to avoid autogrowth of the transaction log files. This works well assuming transactional activity is consistent or predictable throughout…

|

Columnstore Indexes and Statistics

Until the introduction of Columnstore indexes, most DBAs didn’t care much about the creation of statistics on the indexed columns as the statistics are auto created on the indexed columns when the index is created. With Auto Create Statistics turned ON for the database, the optimizer will automatically trigger the creation of statistics when it…

|

Impact of Pre-grown Data & Log Files on the Restore Time of the Database

Today, I was asked the following question by one of my colleague who specializes in Sharepoint If the actual data is 50GB in size & if we create a pre-grown data file of 200GB to avoid autogrow & fragmentation, the database restore time for the pre-grown database should be much larger than the restore time…

|

Troubleshooting SQL Server Hang using system_health Xevents

Recently my customer reported they were seeing intermittent server hangs, connection timeouts and slow performance on their SQL Server instance. The first question I had, do we have PSSDIAG running on the SQL Instance for me to be able to understand the cause of the hang. However since we won’t be running PSSDIAG proactively on…

|

Can number of worker threads in SQL Server exceed max worker threads setting?

Recently while troubleshooting a performance issue on the SQL Instance which was experiencing a very high THREADPOOL wait type I observed something which I was unaware until I hit this issue. While I was looking at the system health xel files captured at the time of the issue, specifically at the query_processing event trace, the…

| | |

SQL Consolidation Considerations !!!

In modern times, with current focus of most organizations on saving costs by means of virtualization and consolidations., It becomes increasingly important for DBAs to ensure that help organization achieve this objective without compromising on the performance, scalability, availability & application compatibility. In this blog post, I would like to give some pointers on 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)…

|

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…