In my previous blog, I discussed about “Which are the queries using a particular index or table?“. Today’s post, I am going to show how you can find all queries with Implicit Conversion in SQL Server. What is an implicit conversion? Implicit conversions occur whenever data with two different data types are being compared. Implicit conversions will often harm […]
Which are the queries using a particular index or table?
Read more
The DMV sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats give you excellent detailed information about how the indexes are being used and what operations are occurring (select, insert, update, delete) in the database. But they don’t tell you a particular index or table is being used by which all the queries. So, when you want to tune the existing […]
Determine Referenced Objects in the Stored Procedures
Read more
It is very common to see in a relational database that stored procedures are using many Tables, Views, Sequence Object, Stored Procedures, Functions, or many more to process the business logic. If you happen to work on a Stored Procedure performance issue which referenced many objects, you might end up asking yourself – “Is the […]
How to Perform Database Hack-Attach
Read more
This blog demonstrates attaching a database on the SQL Server Instance which already has the same name database up and running. Scenario: Suppose, you got a hardware migration activity. The migration activity involves moving the databases from old hardware to new hardware. Let’s say the server got only one big database to be migrated. During […]
Finding Untrusted Foreign Keys and Constraints
Read more
In the previous blog, we discussed the Foreign Keys Constraints and how the CHECK Constraints are useful to verify the data that goes into your tables. But, sometimes when you need to load a lot of data quickly, SQL Server allows you to temporarily disable any CHECK or FOREIGN KEY constraint in order to improve performance. […]
Find Missing Indexes For All SQL Server Databases
Read more
DMV (Database Management View) and DMO (Database Management Objects) were added in 2005. There have been huge improvements in each new edition of SQL Server. It provides us a lot of useful information about SQL Server like – expensive queries, wait type, missing index…., and the list goes on and on… In this blog, I […]
SQL Server Logical Query Processing
Read more
Recently, while reviewing one of the stored procedure performance, I suggested a couple of changes to a developer to improve the performance of the SP. One of the suggestions was to replace DISTINCT with GROUP BY. A few days later, he came back to me with a performance matrix to show that there was no […]
How to Check SQL Server View Utilization
Read more
If you are working on SQL Server optimization delicately, you may get often queries that how to find if any index, table or view is being used in database or not. In recent past, we went live for one of the critical application. The developers decided to check through all the indexes utilization including View indexes […]
What is a Foreign Key & How many foreign keys can you have on a single table?
Read more
Yesterday, I was attending a database design meeting. In the meeting, I was asked one of the questions – “How many foreign keys can you have on a single table?” Honestly, I had no clue about the limit. But, I must acknowledge that it was an excellent question to learn something new. Once the meeting […]
How SQL Compilation Can Prove the Server CPU
Read more
It is relatively easy to spot CPU-bound system. But, the source of CPU pressure is not always easy to pinpoint. The CPU can be driven by many factors on the server such as; Memory pressure which is causing SQL Server to continually move data in and out of the memory. Disk latency Bad indexes Poorly […]