Making my DB faster
-
Hi folks, I have a database in MS SQL Server which is accessed by an application I wrote. In other words, I have control over both the DB and the software. Things have grown so much now that I'm getting worried I might not have set up all the correct indexes in the DB to really optimise the queries. I should probably sit down and go through all of my code to see what queries are being run against the DB and what indexes would optimise those queries but there is such a multitude of queries and it would be very difficult to determine which of these are run frequently and which of them only spradically. Is there a tool that can monitor all the queries that are being run against the DB over a period of time and then analyze the shortcomings in the DB design? Somehow I recall having heard of such a tool but I've never used anyhting of the sort and don't really know where to start looking.
-
Hi folks, I have a database in MS SQL Server which is accessed by an application I wrote. In other words, I have control over both the DB and the software. Things have grown so much now that I'm getting worried I might not have set up all the correct indexes in the DB to really optimise the queries. I should probably sit down and go through all of my code to see what queries are being run against the DB and what indexes would optimise those queries but there is such a multitude of queries and it would be very difficult to determine which of these are run frequently and which of them only spradically. Is there a tool that can monitor all the queries that are being run against the DB over a period of time and then analyze the shortcomings in the DB design? Somehow I recall having heard of such a tool but I've never used anyhting of the sort and don't really know where to start looking.
From the SQL Server Management Studio, under the Tools menu, you will find "SQL Server Profiler". Use this tool to see exactly what SQL statements are being run, then take those statements and run them in the SQL Server Manager, and under the Query menu click on the "Include Actual Execution Plan". After running the query it will show you whether or not an index is used, which tables (or indexes) are being scanned, etc. Cool stuff. Good luck. :thumbsup:
-
From the SQL Server Management Studio, under the Tools menu, you will find "SQL Server Profiler". Use this tool to see exactly what SQL statements are being run, then take those statements and run them in the SQL Server Manager, and under the Query menu click on the "Include Actual Execution Plan". After running the query it will show you whether or not an index is used, which tables (or indexes) are being scanned, etc. Cool stuff. Good luck. :thumbsup:
Thanks a bunch, that looks like exactly the thing I was looking for. The reason I couldn't find it was because, on my machine, I have a SQL express version of the Management Studio from which the Profiler seems to be missing. When I work directly on the machine that runs SQL Server, it is there. Now I need to figure out how to set up a trace and how to make it meaningful. But thanks, I've got something to work from now.
-
Hi folks, I have a database in MS SQL Server which is accessed by an application I wrote. In other words, I have control over both the DB and the software. Things have grown so much now that I'm getting worried I might not have set up all the correct indexes in the DB to really optimise the queries. I should probably sit down and go through all of my code to see what queries are being run against the DB and what indexes would optimise those queries but there is such a multitude of queries and it would be very difficult to determine which of these are run frequently and which of them only spradically. Is there a tool that can monitor all the queries that are being run against the DB over a period of time and then analyze the shortcomings in the DB design? Somehow I recall having heard of such a tool but I've never used anyhting of the sort and don't really know where to start looking.