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
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
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
Working with many customers for performance issues in our DB, we realised that one of the important questions we ask them is about system configuration. So instead of asking this every time, we decided to come up with T-SQL script that can be included in our process document and even L1 support can run it …
Continue reading Getting OS and hardware details using T-SQL
Recently I faced a requirement where I need to find duplicate tables. Our system has hundreds of jobs and all jobs creates set of tables. System is so old and complex no one knows exactly. So, the objective was to find duplicate tables and then start brainstorming about how to redesign current system. Below script …
Pro MSBI 