We all have worked in complex databases, where problem gets complex in exponentially. When we see a single SP is called from UI and start digging, we find that it’s a complex chain of calling SPs and functions. This is a very common scenario in most of the applications. We are facing the same issue …
Continue reading Generating Stored Procedure execution flow for analysis
Let's say we are runnig a complex query and our objective is to tune it for better performance. We know we can find which join is used by optimizer by examining the execution plan, but we all know execution plan provides a ton of other useful information too. let's say we just want a quick …
Continue reading Finding join types in a query for performance tuning
We all have faced situation, where we run the profiler for just couple ofseconds and get the thousands of records captured by profiler, especially inProduction environment. We capture many folds data in profiler when we are notsure about exact filters, data we are looking for, SP and table. I face this situation frequently, especially when …
Continue reading Declutter sql server profiler data – find SPs and tables
Assume the situation where we want to move all or some of the stored procudres to another database. May be for the purpose of back up or development environment to test or test to production. There are many possibilities for this situation.Here our first task will be to get the list of stored procedures, which …
Continue reading Moving Stored Procedure to another database on same server
let's assume, our product is being used by multiple customers. Each customer has unique pattern and work load of using the application. Our experience says low space in physical drive could be a cause for slowness or unresponsiveness. So, it’s very important to know available free space and how long it’s going to last for …
Continue reading Monitoring and Projecting DB space used in SQL Server
Understanding DB load pattern is a very important part of optimization. A good, stored procedure can perform bad during heavy load time and poorly written SP can perform good in idle time. Bottom line is your workload, memory and CPU availability are important consideration for DB optimization. We can use the below script to get …
Continue reading Monitoring Memory and CPU utilization using TSQL
Pro MSBI 