Tips
Buffer Pool Space in SQL Server 2005
I need to determine what objects/structures are consuming the largest amount of space at a given time (or over time) within the SQL Server buffer pool. How can I achieve this in SQL Server 2005?
Collect and store historical SQL Server performance counter data with DMVs
I'd like to capture and store specific SQL Server performance counters over time, but I don't want to have to set up Performance Monitor to run on each of my SQL Servers - I have too many, and it would take too much hands-on-keyboard computer time to put that plan in play. In this tip, we cover how to use the DMVs to collect and store historical performance data.
Collecting Query Statistics for SQL Server 2005
With SQL Server 2005 DMVs, we can easily find query performance statistics. Every DBA has a favorite script to find query performance stats. I also have one. But, are all the queries captured by the DMVs? In this tip I will walk through capturing some of this data and show what is capture and what is not captured by the DMVs.
Collecting SQL Server performance counter data for trending
In my previous tip in this series, I introduced you to the sys.dm_os_performance_counters DMV and showed you how to return information from it. This data is displayed with the current values, so what happens if you want to look at counters over time? In this tip I show how you can use the DMV to collect data for trending.
Creating IO performance snapshots to find SQL Server performance problems
I/O is one of the most time consuming activities in SQL Server. If you can reduce the I/O wait time then you can improve performance. This can be done with indexing and tuning queries, but I/O issues may also be at the file level. In this tip we look at how to identify specific files that consume the most I/O activity using a SQL Server DMV and creating snapshots for analysis.
Determine objects consuming the largest amount of space in the SQL Server buffer pool
Determine SQL Server memory use by database and object
For many people, the way that SQL Server uses memory can be a bit of an enigma. A large percentage of the memory your SQL Server instance utilizes is consumed by buffer pool (essentially, data). Without a lot of digging, it can be hard to tell which of your databases consume the most buffer pool memory, and even more so, which objects within those databases. This information can be quite useful, for example, if you are considering an application change to split your database across multiple servers, or trying to identify databases that are candidates for consolidation.
Dynamic Management Views and Functions in SQL Server 2005
With SQL Server 2000 it was difficult to capture real time statistics on many of the core database engine features without issuing DBCC commands, running Profiler or scheduling the execution of custom scripts. With add-on features such as Full Text Search it was difficult to capture metrics on portions of the application and troubleshooting performance was less than efficient. From SQL Server 2000 to 2005, the number of add-on features has grown and troubleshooting overall SQL Server performance has the potential to be even more of an issue.
Finding a SQL Server process percentage complete with DMVs
Some tasks that are run in SQL Sever take a long time to run and it is sometimes difficult to tell whether these tasks are progressing or not. One common way of determining that status is to look at the data returned from sp_who2 or sp_lock to ensure that things are still working and the process is not hung. With SQL Server 2005 several dynamic management views have been added, so let us take a look at some of these and how they can assist.
How to Find Keywords in SQL Server Stored Procs and Functions
How many times have you had to troubleshoot or make changes to an existing database that is not documented properly or completely? Even need to look for a specific stored procedure that references a specific table or process? If you have intimate knowledge of the database then this may not be much of an issue for you. What happens if this database is from an external developer, a turn-key solution provider, or developed by another individual within your company? If you utilize the INFORMATION_SCHEMA.ROUTINES view to research and troubleshoot this issue, the task goes from daunting to a cake-walk.
Identify last statement run for a specific SQL Server session
I was reading a recent blog post regarding returning information on the latest query executed for a given session. The post offered up a couple options to return the last query statement executed, settling upon querying the sys.sysprocesses system compatibility view, but another way that this can be done is through the Dynamic Management Views and Functions. The process for doing so is quite straight-forward and works in all versions of Microsoft SQL Server since DMOs (dynamic management objects) were integrated into SQL Server.
Identifying the input buffer in SQL Server 2000 vs SQL Server 2005
When troubleshooting a potential SQL Server performance problem, it is difficult to know if the code is problematic without being able to review all of the code. You could ask the Developer for the code you suspect, run Profiler to capture code, leverage a third party tool for the data collection or try to leverage the native T-SQL commands to review the code. Historically tracking down the code was available with sp_who2, DBCC INPUTBUFFER and fn_get_sql. With the introduction of SQL Server 2005, is a simpler means with more bells and whistles available with the dynamic management views and functions?
New DMF for SQL Server 2008 sys.dm_fts_parser to parse a string
Many times we want to split a string into an array and get a list of each word separately. The sys.dm_fts_parser function will help us in these cases. More over, this function will also differentiate the noise words and exact match words. The sys.dm_fts_parser can be also very powerful for debugging purposes. It can help you check how the word breaker and stemmer works for a given input for Full Text Search.
Session State Settings for Cached SQL Server Query Plans
A cached query plan will not be used if the query is different than the original query used to generate the cached plan - makes sense - but what if the query is identical? Well even then the query optimizer may not used the cached query plan. Why? Because all the session settings for the current query request must match those from the first time the query plan was generated and cached. So the question is how can you determine what settings are associated with the cached plan? The answer is in the SQL Server Dynamic Management Objects.
SQL Server Monitoring Scripts with the DMVs
As a SQL Server DBA, dedicated to monitoring and administering many busy OLTP production environments, I always find myself trying to answer questions like: What was SQL Server doing last night when the end users were experiencing slow application response times? Was the issue from SQL Server? Or was it a network or a application issue? The answers to such questions are never easy to identify. If I only knew what SQL Server was doing at that specific point in time. If I knew what SQL Server was doing at that point in time, it may help me understand if the issues was a SQL Server performance issue or not . So how can I figure this out? If you do not have a third party monitoring tool in place, the only way is to constantly monitor SQL Se
SQL Server Performance Counter DMV sys.dm_os_performance_counters
I make no attempt to hide my dislike for Performance Monitor. Don't get me wrong - it is a good product, but I'm a Transact-SQL kind of guy and I'm at the point after this many years as a SQL Server Professional where I expect to get all my information at the bottom of a white screen in tabular format. Yes, I'm a SQL Server Management Studio Junkie and I will not change any time soon. What is a guy like me supposed to do when it comes to getting performance information - specifically performance counters that I would expect to see in Performance Monitor (aka PerfMon) - in SSMS?
SQL Server Resource Database Values in DMVs
Multiple queries against system dynamic management views and catalog views result in database ID's and names with a NULL value. Is something wrong with my queries? How can the database name or ID be NULL? Can I modify my queries to capture the correct values?
SQL Server sys.dm_os_wait_stats DMV Queries
I get so frustrated when trying to determine where to start when a user comes to me and tells me that "the database is slow". Launching Performance Monitor and running a trace in Profiler is inefficient. Unless I know what I'm looking for to begin with it's hard to narrow things down using those tools! Do you have any suggestions for quickly determining if I should be looking at CPU, Memory, or I/O issues without the needle v. haystack dilemma that comes with diving right in to Perfmon and Profiler?
Understanding Catalog Views in SQL Server 2005 and 2008
SQL Server stores meta data about its configuration options, objects, data type, constraints, etc. in system tables, which cannot be directly queried in SQL Server 2005 and 2008. To access this meta data, SQL Server offers several options. The option I have selected in this tip is the Catalog Views. These provide the most efficient and generic interface to obtain, transform and present customized forms of the persisted system meta data. In this tip, I am going to discuss in details about Catalog Views and how they differ from other options like using Compatibility Views, Information Schema Views, etc.
Understanding Dynamic Management Views and Functions in SQL Server 2008
Performance monitoring and optimization is an inseparable part of a DBA's activities. To optimize a poorly performing system/query or to troubleshoot the performance issues you need to know the root cause. Prior to SQL Server 2005, we had several system tables to monitor the state of the system. Though these system tables are still available in SQL Server 2005 and in later versions but SQL Server 2005 introduced whole new set of Dynamic Management Views (DMV) and Dynamic Management Functions (DMF) to monitor the health of a SQL Server instance, diagnose and tune the performance issues.
Useful management information from SQL Server DMV sys.dm_os_sys_info
I'm starting to get interested with the Dynamic Management Views in Microsoft SQL Server. In this tip we look at the DMV sys.dm_os_sys_info and what information you can query and use to manage your SQL Server.
Using SQL Server DMVs to Identify Missing Indexes
In a previous tip, Deeper insight into unused indexes for SQL Server, we discussed how to identify indexes that have been created but are not being used or used rarely. Now that I know which indexes I can drop, what is the process to identify which indexes I need to create. I can run the Database Tuning Advisor or examine the query plans, but is there any easier way to determine which indexes may be needed?
Waitstats performance metrics in SQL 2000 vs SQL Server 2005
From SQL Server 2000 to 2005, many of the core SQL Server system metrics have migrated from static commands to dynamic management views and functions that can be queried to gather statistics in real time. The new dynamic management views and functions offer a great deal of flexibility to troubleshoot system issues, especially for WAITSTATS.
Top 10
Determine SQL Server memory use by database and object
For many people, the way that SQL Server uses memory can be a bit of an enigma. A large percentage of the memory your SQL Server instance utilizes is consumed by buffer pool (essentially, data). Without a lot of digging, it can be hard to tell which of your databases consume the most buffer pool memory, and even more so, which objects within those databases. This information can be quite useful, for example, if you are considering an application change to split your database across multiple servers, or trying to identify databases that are candidates for consolidation.
SQL Server Monitoring Scripts with the DMVs
As a SQL Server DBA, dedicated to monitoring and administering many busy OLTP production environments, I always find myself trying to answer questions like: What was SQL Server doing last night when the end users were experiencing slow application response times? Was the issue from SQL Server? Or was it a network or a application issue? The answers to such questions are never easy to identify. If I only knew what SQL Server was doing at that specific point in time. If I knew what SQL Server was doing at that point in time, it may help me understand if the issues was a SQL Server performance issue or not . So how can I figure this out? If you do not have a third party monitoring tool in place, the only way is to constantly monitor SQL Se
SQL Server sys.dm_os_wait_stats DMV Queries
I get so frustrated when trying to determine where to start when a user comes to me and tells me that "the database is slow". Launching Performance Monitor and running a trace in Profiler is inefficient. Unless I know what I'm looking for to begin with it's hard to narrow things down using those tools! Do you have any suggestions for quickly determining if I should be looking at CPU, Memory, or I/O issues without the needle v. haystack dilemma that comes with diving right in to Perfmon and Profiler?
Creating IO performance snapshots to find SQL Server performance problems
I/O is one of the most time consuming activities in SQL Server. If you can reduce the I/O wait time then you can improve performance. This can be done with indexing and tuning queries, but I/O issues may also be at the file level. In this tip we look at how to identify specific files that consume the most I/O activity using a SQL Server DMV and creating snapshots for analysis.
Using SQL Server DMVs to Identify Missing Indexes
In a previous tip, Deeper insight into unused indexes for SQL Server, we discussed how to identify indexes that have been created but are not being used or used rarely. Now that I know which indexes I can drop, what is the process to identify which indexes I need to create. I can run the Database Tuning Advisor or examine the query plans, but is there any easier way to determine which indexes may be needed?
SQL Server Performance Counter DMV sys.dm_os_performance_counters
I make no attempt to hide my dislike for Performance Monitor. Don't get me wrong - it is a good product, but I'm a Transact-SQL kind of guy and I'm at the point after this many years as a SQL Server Professional where I expect to get all my information at the bottom of a white screen in tabular format. Yes, I'm a SQL Server Management Studio Junkie and I will not change any time soon. What is a guy like me supposed to do when it comes to getting performance information - specifically performance counters that I would expect to see in Performance Monitor (aka PerfMon) - in SSMS?
Collect and store historical SQL Server performance counter data with DMVs
I'd like to capture and store specific SQL Server performance counters over time, but I don't want to have to set up Performance Monitor to run on each of my SQL Servers - I have too many, and it would take too much hands-on-keyboard computer time to put that plan in play. In this tip, we cover how to use the DMVs to collect and store historical performance data.
Understanding Dynamic Management Views and Functions in SQL Server 2008
Performance monitoring and optimization is an inseparable part of a DBA's activities. To optimize a poorly performing system/query or to troubleshoot the performance issues you need to know the root cause. Prior to SQL Server 2005, we had several system tables to monitor the state of the system. Though these system tables are still available in SQL Server 2005 and in later versions but SQL Server 2005 introduced whole new set of Dynamic Management Views (DMV) and Dynamic Management Functions (DMF) to monitor the health of a SQL Server instance, diagnose and tune the performance issues.
Collecting SQL Server performance counter data for trending
In my previous tip in this series, I introduced you to the sys.dm_os_performance_counters DMV and showed you how to return information from it. This data is displayed with the current values, so what happens if you want to look at counters over time? In this tip I show how you can use the DMV to collect data for trending.
Understanding Catalog Views in SQL Server 2005 and 2008
SQL Server stores meta data about its configuration options, objects, data type, constraints, etc. in system tables, which cannot be directly queried in SQL Server 2005 and 2008. To access this meta data, SQL Server offers several options. The option I have selected in this tip is the Catalog Views. These provide the most efficient and generic interface to obtain, transform and present customized forms of the persisted system meta data. In this tip, I am going to discuss in details about Catalog Views and how they differ from other options like using Compatibility Views, Information Schema Views, etc.
Last 10
Determine SQL Server memory use by database and object
For many people, the way that SQL Server uses memory can be a bit of an enigma. A large percentage of the memory your SQL Server instance utilizes is consumed by buffer pool (essentially, data). Without a lot of digging, it can be hard to tell which of your databases consume the most buffer pool memory, and even more so, which objects within those databases. This information can be quite useful, for example, if you are considering an application change to split your database across multiple servers, or trying to identify databases that are candidates for consolidation.
Creating IO performance snapshots to find SQL Server performance problems
I/O is one of the most time consuming activities in SQL Server. If you can reduce the I/O wait time then you can improve performance. This can be done with indexing and tuning queries, but I/O issues may also be at the file level. In this tip we look at how to identify specific files that consume the most I/O activity using a SQL Server DMV and creating snapshots for analysis.
Useful management information from SQL Server DMV sys.dm_os_sys_info
I'm starting to get interested with the Dynamic Management Views in Microsoft SQL Server. In this tip we look at the DMV sys.dm_os_sys_info and what information you can query and use to manage your SQL Server.
Collect and store historical SQL Server performance counter data with DMVs
I'd like to capture and store specific SQL Server performance counters over time, but I don't want to have to set up Performance Monitor to run on each of my SQL Servers - I have too many, and it would take too much hands-on-keyboard computer time to put that plan in play. In this tip, we cover how to use the DMVs to collect and store historical performance data.
New DMF for SQL Server 2008 sys.dm_fts_parser to parse a string
Many times we want to split a string into an array and get a list of each word separately. The sys.dm_fts_parser function will help us in these cases. More over, this function will also differentiate the noise words and exact match words. The sys.dm_fts_parser can be also very powerful for debugging purposes. It can help you check how the word breaker and stemmer works for a given input for Full Text Search.
Collecting SQL Server performance counter data for trending
In my previous tip in this series, I introduced you to the sys.dm_os_performance_counters DMV and showed you how to return information from it. This data is displayed with the current values, so what happens if you want to look at counters over time? In this tip I show how you can use the DMV to collect data for trending.
SQL Server Performance Counter DMV sys.dm_os_performance_counters
I make no attempt to hide my dislike for Performance Monitor. Don't get me wrong - it is a good product, but I'm a Transact-SQL kind of guy and I'm at the point after this many years as a SQL Server Professional where I expect to get all my information at the bottom of a white screen in tabular format. Yes, I'm a SQL Server Management Studio Junkie and I will not change any time soon. What is a guy like me supposed to do when it comes to getting performance information - specifically performance counters that I would expect to see in Performance Monitor (aka PerfMon) - in SSMS?
Understanding Dynamic Management Views and Functions in SQL Server 2008
Performance monitoring and optimization is an inseparable part of a DBA's activities. To optimize a poorly performing system/query or to troubleshoot the performance issues you need to know the root cause. Prior to SQL Server 2005, we had several system tables to monitor the state of the system. Though these system tables are still available in SQL Server 2005 and in later versions but SQL Server 2005 introduced whole new set of Dynamic Management Views (DMV) and Dynamic Management Functions (DMF) to monitor the health of a SQL Server instance, diagnose and tune the performance issues.
SQL Server sys.dm_os_wait_stats DMV Queries
I get so frustrated when trying to determine where to start when a user comes to me and tells me that "the database is slow". Launching Performance Monitor and running a trace in Profiler is inefficient. Unless I know what I'm looking for to begin with it's hard to narrow things down using those tools! Do you have any suggestions for quickly determining if I should be looking at CPU, Memory, or I/O issues without the needle v. haystack dilemma that comes with diving right in to Perfmon and Profiler?
Understanding Catalog Views in SQL Server 2005 and 2008
SQL Server stores meta data about its configuration options, objects, data type, constraints, etc. in system tables, which cannot be directly queried in SQL Server 2005 and 2008. To access this meta data, SQL Server offers several options. The option I have selected in this tip is the Catalog Views. These provide the most efficient and generic interface to obtain, transform and present customized forms of the persisted system meta data. In this tip, I am going to discuss in details about Catalog Views and how they differ from other options like using Compatibility Views, Information Schema Views, etc.
