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
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 …
Index Utilization Report One day my manager came and told me that he need Index Utilization Report. He felt that during development of the project we have created so many indexes but he is not sure about how useful those are. So he wants a report which shows all the Indexes on user defined table …
If you are reading this article, I assume that you have basic knowledge of Index, its types and usage. Over the years of development and chasing high performance we build and update many indexes. Due to shear number or complexity for forget to keep track of number, quantity and utilization of the indexes. For index …
This may feel very simple to many developers and yes it is, but I saw this request somewhere in community website.So thought of creating a very simple example. Here I am using AdventureWorks2014 database, we have table [AdventureWorks2014].[HumanResources].[Employee] and we will use [HireDate] column. I am adding two new columns named HiredMonthName and HiredMonthNumber to make our example clear and …
Pro MSBI 