Browse by Tags

All Tags » sql server » dmv » performance (RSS)
SQL Server - Precision Performance techniques using RML Utilities
Published 9 June 8 7:50 AM | SSQA.net
As a DBA, you probably often find yourself striving or struggling to improve the performance of SQL Server instance queries (might be smaller or complex ones). By default in order to get further analysis on the system's performance you need to perform...
Filed under: , , , , , , , , ,
SQL Server 2005 DMV - quick information to find resource allocation & DDL bottleneck
Published 9 June 8 3:29 AM | SSQA.net
Initially I preferred to put this blog post within Performance tuning blog section here, but as it relates to the TSQL script thought this is best place to go. Anyways, if you have a performance problem the foremost option is to find whether the server...
Filed under: , , , , , ,
SQL Server Performance issues with Fragmentation and heavy usage of TEMPDB?
Published 15 May 8 3:59 AM | SSQA.net
Whenever a performance issue occurs on the SQL Server database best option for diagnosing and troubleshooting common problems by using publicly available tools such as Profiler, System Monitor (Perfmon), and Dynamic Management Views (DMVs) in SQL Server...
Filed under: , , , , , , ,
SQL Server 2005 PERFMON counter spikes in Transactions/sec and Buffer Cache Hit ratio, what does it say?
Published 14 March 8 4:16 AM | SSQA.net
Recently I was involved in one of the Performance Audit exercise at a client's place where they complain about Server CPU is always HIGH and what they have observed is even the physical disk where TEMPDB is located has been used extensively, as they...
Filed under: , , , , , , , , , , , ,
SQL Server error: messages are appearing when the working set of SQL Server 2005 process reaches 50 percent of the memory that is committed to the process.
Published 7 March 8 4:54 AM | SSQA.net
Have you seen the above message within the SQL Server error logs? If not then no need to worry and make sure to continue your performance monitoring tasks. So when to be concerned! It is evident that SQL Server 2005 component handles memory differently...
Filed under: , , , , , , , , , , , , ,
SQL Server 2005 DMV - how it can help to consider whether index is useful or not?
Published 4 February 8 8:6 AM | SSQA.net
When it comes to performance, for a DBA indexes are the first one to come to mind in order to fine tune the tasks on the database. In this series we have already covered this huge topic of indexing and how best you can make use of Dynamic Management Views...
Filed under: , , , , , ,
SQL Server Query Performance - think about page split and fragmentation, measures to reduce the behaviour and best use of dm_db_index_physical_stats DMV
Published 14 January 8 2:22 AM | SSQA.net
It is an universal truth in the database field that due to the fragmentation and page split the performance will be affected even for a simple [ SELECT <ColumnName> from.... ] type of query. So what you need to check or take action in terms of DBA...
Filed under: , , , , , , , , ,
SQL Server 2005: Deny Server-Level Dynamic Management Views to public group
Published 21 December 7 7:47 AM | SSQA.net
As you are aware DMV - Dynamic management views are very good to provide server-level information and some of them with detailed information on the execution environment of the database engine. Just to introduce about DMVs, there are two types of dynamic...
Filed under: , , , , , , , , , ,
SQL Server: How do I identify the queries that are worth tuning?
Published 16 November 7 7:2 AM | SSQA.net
Have you ever asked this question about how do I identify the queries that worth tuning? The simple answer it is neither easy or hard to obtain such information if you have managed your SQL Server by deploying industry best practices (wherever possible...
Filed under: , , , , , , , , ,
Identifying top 20 most expensive queries in terms of read I/O (referred from Technet Magazine)
Published 15 November 7 7:16 AM | SSQA.net
It is worth mentioning the valueable query I have been through when referring to Technet Magazine, the following query has given me useful information in finding out what are my top 20 most expensive queries that are consuming most of disk I/O (read &...
Filed under: , , , , , , , , , , ,
sys.dm_os_schedulers - useful to find on whether your SQL Server engine is weakening!
Published 17 August 7 8:4 AM | SSQA.net
The system DMV sys.dm_os_schedulers is an useful DMV to find such as running tasks & active workers threads etc. This is required to monitor the system state is heavily loaded or not, in particular this view will help you identify if there is any...
Filed under: , , , , , , ,
List of CPU based DMVs in SQL Server 2005 - best used for hardware analysis
Published 5 August 7 2:32 PM | SSQA.net
Dynamic Management Views (DMV) are very useful to get server state information to monitor the status, but not always you have to still continue in monitoring using SYSMON & PROFILER that are available by default with SQL installation. The engine within...
Filed under: , , , , ,
Whats new with SQL 2008 July CTP - new DMVs for memory management
Published 4 August 7 4:40 AM | SSQA.net
As this is the 2nd instalment of public CTP of SQL Server 2008 we will be expecting more information on 'whats new' and 'whats not (deprecated)'. For me (majority of you too) having handful information with DMVs is essential, in this regad...
Filed under: , , , , ,
Why query execution timeout happens and find queries that have granted memory or waiting on memory?
Published 23 July 7 4:44 AM | SSQA.net
Have you ever identified the reasons for why and how does a query execution timeout happens within your SQL Server environment. For the beginners I would like to explain that b efore executing a query, SQL Server estimates how much memory it needs to...
Filed under: , , , , , , , ,
Knowing about performance impact of simple use of procedures
Published 13 July 7 9:42 AM | SSQA.net
If you ask an experience DBA about the reasons for performance loss then the answer should be straight to look for SQL Server, Operating System and hardware counters for further investigation. Simultaneous access to shared resources causes bottlenecks...
Filed under: , , , , , ,
SQL Server 2005 get a list of missing indexes and identify useful indexes
Published 25 June 7 7:43 AM | SSQA.net
It is evident that well-designed indexes will help a lot in attaining performance for a poorly performing queries, this can also reduce disk I/O operations by consuming lesser resources on the server. Maily indexes are helpful during SELECT queries and...
Filed under: , , , ,
SQL Server Cursors usage and performance - cursed
Published 19 June 7 9:34 AM | SSQA.net
If you have a requirement to poll through 'n' number of rows then immediately you would think about Cursors in SQL Server. As the solution using any programming language that loops recordsets to build the attendance list that works ok, but really...
Filed under: , , , , , , ,
Handling and troubleshooting memory issues in a SQL Server 2005 instance
Published 19 June 7 4:29 AM | SSQA.net
We had a major issue on one of the SQL Server instance with the memory and getting following error message intermittenlty: [DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation. Eventually I was able...
Filed under: , , , , ,
Get a blocking list for the objects & indexes using DMV
Published 13 June 7 9:44 AM | SSQA.net
Yet another help from DMV I would like to share here, say on a SQL Instance you are getting low-level I/O and blocking intermittently. For the I/O issues using SYSMON (PERFMON) is ideal to get more information during the busy times and low-usage times...
Filed under: , , , , ,
Get backup and restore performance stats with a DMV
Published 12 June 7 8:25 AM | SSQA.net
Normally the backup and restore operation will be quick enough or depends upon the server configuration to complete. Say if you have started with a 2GB size of database and over the time as that database is updated, the amount of data that is included...
Filed under: , , , , , ,
More Posts Next page »