Category Archives for "Performance Tuning"

Jan 07

Disk Metrics – Why PerfMon’s, SQL Server’s Filestats, and Disk Vendors’ Values Sometimes Appear to Disagree

By Jeffry Schwartz | Expert , Performance Tuning , SQL Server , Windows

— By Jeffry Schwartz Whenever multiple data sources are used, actual or perceived discrepancies can arise. Sometimes this is due to the location of the data collector, e.g., Windows, SQL Server, or the disk array. Other times, it is due to the collection rate and whether the information is a simple recording of continuously incremented […]

Continue reading
Dec 17

Stored procedure execution info with query plan

By Lori Brown | Expert , Performance Tuning , SQL Administration , SQL Server

Sometimes it is good to have some queries in your toolkit that give you information on how things are running that includes the actual execution plan. While looking at Glenn Berry’s diagnostic query scripts (http://www.sqlskills.com/blogs/glenn/sql-server-diagnostic-information-queries-for-september-2015/), which are very good btw, I found queries that will list stored procedure statistics but none had the execution plan […]

Continue reading
Oct 15

Using Common Table Expressions to Improve Query Performance

By Jeffry Schwartz | Advice , Intermediate , Performance Tuning , SQL Development , SQL Server

-by Jeffry Schwartz Queries with multiple join clauses create particularly difficult issues when they perform poorly. Join ordering is extremely important because if a join creates a huge interim result set at the beginning of the query execution, SQL Server must continue to handle this result set until it can be trimmed down with filtering […]

Continue reading
Jun 03

Changes to System Views in SQL 2014

By Lori Brown | Helpful Scripts , Miscellaneous , Performance Tuning , SQL Server , Tip of the Month

There are two new system views and two system views that have new columns added in SQL 2014. Most notable is sys.databases since this is widely used.   sys.xml_indexes – Has 3 new columns: xml_index_type, xml_index_type_description, and path_id. https://msdn.microsoft.com/en-us/library/ms176003.aspx sys.databases – Has 3 new columns: is_auto_create_stats_incremental_on, is_query_store_on, and resource_pool_id. https://msdn.microsoft.com/en-us/library/ms178534.aspx sys.column_store_row_groups – New view.  Provides clustered […]

Continue reading
Feb 16

Top PerfMon Performance Counters for Determining Causes of Slow Response Times

By Jeffry Schwartz | Performance Tuning , SQL Server , Windows

Introduction Have you ever been placed in the position of being forced to address user complaints about how slow the system is performing without really knowing exactly where to begin looking? Some analysts begin with Task Manager or viewing the Windows event log. Others who are working with SQL Server search the ErrorLog for problems […]

Continue reading
Sep 07

September 2010 Tip of the Month

By SQLRx Admin | Performance Tuning , SQL Administration , SQL Maintenance , SQL Server , Tip of the Month

Performance Monitor: Monitor the performance counters SQLServer:Buffer Manager (Page reads/sec) and SQLServer:Buffer Manager (Page writes/sec) to detect poor performance. These counters measure physical IOs and not logical IOs. Heavy activity can indicate lack of database memory, a poor table implementation or can show that applications are not accessing a database correctly. Monitoring these counters will […]

Continue reading
>