Date: Tue, 3 Feb 2004 12:31:56 -0700 From: "SQL-Server-Performance.Com" <webmaster@sql-server-performance.com> To: "Joseph Mallett" <freebsd-chat@freebsd.org> Subject: SQL-Server-Performance.Com Newsletter -- February 3, 2004 Message-ID: <1048810526-22004223193156343@alentus.com>
next in thread | raw e-mail | index | archive | help
================================================================= **SQL-Server-Performance.Com Newsletter** ================================================================= February 3, 2004 ================================================================= Editor: Brad M. McGehee, MVP, MCSE+I, MCSD E-Mail: mailto:webmaster@sql-server-performance.com ================================================================= URL: http://www.sql-server-performance.com/ ================================================================= Information on how to subscribe, unsubscribe, and to change your e-mail address is at the bottom of this newsletter. This is a 100% opt-in newsletter. ================================================================= ================================================================= **Sponsor's Message** ================================================================= COMPARE AND SYNCHRONIZE SQL SERVER DATABASES Use Red Gate's tools for SQL Server databases for all your comparison and synchronization tasks. Tools include: --SQL Compare: Compare and synchronize database structures --SQL Data Compare: Compare and synchronize data in databases --DTS Compare: Compare SQL Server settings, jobs, logins and DTS packages --SQL Toolkit: Automate SQL Compare and SQL Data Compare Visit http://www.red-gate.com/sql/summary.htm for a FREE trial, or contact mailto:sales@red-gate.com ================================================================= ================================================================= **In This Issue** ================================================================= -- New Information Published on SQL-Server-Performance.Com -- New and Updated SQL Server Performance Tips -- January Forum Contest Winners Announced -- Learn & Win: February Forum Contest With FREE Software ================================================================= ================================================================= **New Information Published on SQL-Server-Performance.Com** ================================================================= Here are some new items that were recently posted to SQL-Server-Performance.Com: -- New Article: Transferring SQL Server Statistics From One Database to Another --New for Writers: If you submit an article to SQL-Server- Performance.Com in 2004, and it is published, you will receive a free copy of LeadByte Software's Network Performance Suite, worth $977 If you haven't seen these yet, check them out at: http://www.sql-server-performance.com/ ================================================================= ================================================================= **Sponsor's Message** ================================================================= IDENTIFY BLOCKED SQL SERVER PROCESSES THAT HINDER PERFORMANCE Try our new Stealth Blocked Process Monitor today and know what SQL statements and database locks are occurring! Product pinpoints database SQL-causing blocked process conditions resulting in degrading end-user response time and poor performance. The product analyzes, graphs and archives the following: -- Blocked process tree with blocked SPIDs, SQL text, locks and more. -- Ranks SQL statements and applications most frequently involved in SQL transaction blocking. -- Reconstruction of blocking incidents with complete blocked process tree details. -- Multi-level e-mail and pager alerts of blocked process conditions for mixed OLTP, data warehousing, reporting and application environments. Download your free trial today at http://www.sqlpower.com Sql Power Tools is the industry recognized leader in ZERO IMPACT database monitoring of end-user service levels, SQL performance, top N SQL analysis, blocked process analysis, and data warehousing end-user data usage analysis. ================================================================= ================================================================= **SQL Server Performance Tuning and Optimization Tips** The SQL Server performance tips listed below were recently added or updated on the website. ================================================================= **Blocking** To help identify and correct queries that cause blocking locks, you need to find out what the query looks like that is causing the blocking lock. Before you can do this, you must first identity the SPID that is causing the blocking. Once you have done that, there are two ways to view the query that is causing the blocking lock First, from Enterprise Manager, in the "Process Info" window located under "Current Activity," right-click on the SPID causing the blocking and choose "Properties." This will display the query. Second, from Query Analyzer, enter this code to reveal the query causing the blocking lock: DBCC INPUTBUFFER (<spid>) Once you have identified the query that is causing the blocking lock, you can begin researching it to see if there is anything you can do to modify the query to avoid blocking in the future. [7.0, 2000] ***** To help identify the type of lock that a blocking lock is holding, you must first identify the SPID that is causing the blocking, Once you have that, there are two ways to view the type of lock being held by the blocking lock: First, from Enterprise Manager, in the "Locks/Process ID" window located under "Current Activity," locate the SPID causing the blocking lock, and click on it. The type of lock will be displayed in the right-hand window. Second, run the command: sp_lock in Query Analyzer. You will then have to match the SPID of the block lock to the SPID listed in the results of this command. Knowing the type of lock held by the blocking lock can help you figure out why the query in question is causing a blocking lock. [7.0, 2000] ***** One way to help identify blocking locks is to use the SQL Server Profiler. The Profiler is useful for capturing blocking locks because it can capture blocking locks over time, unlike the Enterprise Manager, which only shows blocking locks as of the current instant. In addition, the query that is available from the INPUTBUFFER may not be enough information to diagnose a blocking problem. Sometimes, queries that run just before the query that is causing the blocking is related to the blocking problem. By performing a Profiler Trace, you can see all the queries and other activity that precede a blocking lock. In order to use the trace data, you will have to know the SPID that caused the blocking lock, and then look up the data from the trace for this one particular SPID. Below are two different Profiler configurations, one for SQL Server 7.0 and one for SQL Server 2000. SQL Server 7.0 Profiler Configuration Events: Error and Warnings: Exception Misc: Attention Misc: Execution Plan Sessions: Connect Sessions: Disconnect Sessions: Exiting Connection TSQL: RPC:Starting TSQL: RPC:Completed TSQL: SQL:BatchStarting TSQL: SQL:BatchCompleted Stored Procedures: SP:StmtStarting Stored Procedures: SP:StmtCompleted Transactions: SQLTransaction Data Columns: Group By: SPID Event Class Text Integer Data Binary Data Application Name NT User Name SQL User Name Start Time End Time Connection ID Filters: Trace Event Criteria: Severity (type 24 in the Maximum box) Add other filters as desired to reduce a flood of too much data SQL Server 2000 Profiler Configuration Events: Error and Warnings: Exception Error and Warnings: Attention Performance: Execution Plan Sessions: Exiting Connection Stored Procedures: RPC: Starting Stored Procedures: RPC: Completed Stored Procedures: SP: Starting Stored Procedures: SP: Completed Stored Procedures: SP: StmtStarting Stored Procedures: SP: StmtCompleted Transactions: SQLTransaction TSQL: SQL:BatchStarting TSQL: SQL:BatchCompleted Data Columns Group: SPID EventClass TextData IntegerData BinaryData ApplicationName NTUserName LoginName StartTime EndTime Filters: Trace Event Criteria: Severity (type 24 in "Less than or equal" box) Add other filters as desired to reduce a flood of too much data One of the best ways to use the Profiler is to begin a trace, duplicate the activity that causes the blocking lock, identify the SPID of the blocking lock in Enterprise Manger, and then stop the trace. Next, look up the SPID in the trace, viewing all of the activity that occurred up to the blocking lock occurring. [7.0, 2000] ================================================================= **Sponsor's Message** ================================================================= FREE White Paper on Data Auditing Techniques from Lumigent Auditing data on a Microsoft SQL Server database is of paramount concern for any database professional. You may be called upon to identify the source of data modifications, or changes in permissions or schema and verify how changes were made. Or report on who accessed certain tables and modified them over a period of time. Triggers and applications modifications have known limitations and can result in performance overhead. This white paper outlines the shortcomings of traditional auditing techniques and presents a comprehensive solution for monitoring and auditing your database activity - all while avoiding performance issues on your critical systems. Download it now, compliments of Lumigent Technologies: http://www.lumigent.com/go/sd15 ================================================================= **Cursors** If you need to perform a JOIN as part of your cursor, keyset and static cursors are generally faster than dynamic cursors, and should be used when possible. [6.5, 7.0, 2000] ***** If a transaction you have created contains a cursor (try to avoid this if at all possible), ensure that the number of rows being modified by the cursor is small. This is because the modified rows may be locked until the transaction completes or aborts. The greater the number of rows being modified, the greater the locks, and the higher the likelihood of lock contention on the server, hurting performance. [6.5, 7.0, 2000] ***** Consider using asynchronous cursors if you expect your result set to be very large. This allows you to continue processing while the cursor is still being populated. While it may not actually speed up your application, it should give the appearance to your end users that something is happening sooner that if they have to wait until the entire cursor is populated. [6.5, 7.0, 2000] ***** If you have to use a cursor, break out of the cursor loop as soon as you can. If you find that a problem has occurred, or processing has ended before the full cursor has been processed, then exit immediately. [6.5, 7.0, 2000] ***** If you are using the same cursor more than once in a batch of work, (or within more than one stored procedure), then define the cursor as a global cursor by using the GLOBAL keyword. By not closing or deallocating the cursor until the whole process is finished, a fair amount of time will be saved, as the cursor and the data contained will already be defined, ready for you to use. [7.0, 2000] ================================================================= **Sponsor's Message** ================================================================= IntelliVIEW -- Interactive Reporting Tool for SQL Server IntelliVIEW is an easy-to-use reporting solution that allows you to create rich & interactive reports from your SQL Server databases and integrate them into your applications. Design interactive reports with ease; integrate reporting into .NET, Java, and COM applications; analyze information in real-time; and make faster, better-informed decisions. Integrate Reporting into your SQL Server applications: --Create virtually any type of report--summary, cross-tabs, charts, etc. --Manipulate data using convenient drag & drop facilities. --Slash development time for creating reports by over 75%! --Publish reports easily across the web. --Export reports to popular formats like xls, pdf, rtf, etc. --Print Professional looking reports using the WYSIWYG printing features. --Absolutely No Client Licensing Fees! Download FREE client at http://www.intelliview.com/go/sqlperf ================================================================= **Database Options** If a database will be used for read-only purposes only, such as being used for reporting, consider changing the "read-only" setting to on (the default setting is off). This will eliminate the overhead of locking, and in turn, potentially boost the performance of queries that are being run against it. If you need to modify the database, you can also turn the setting off, make your change, then turn it back on. [6.5, 7.0, 2000] ***** When "auto create statistics" is turned on for a database (which it is by default), statistics are automatically created on all columns used in the WHERE clause of a query. This occurs when a query is optimized by the Query Optimizer for the first time, assuming the column doesn't already have statistics created for it. The addition of column statistics can greatly aid the Query Optimizer so that it can create an optimum execution plan for the query. If this option is turned off, then missing column statistics are not automatically created, when can mean that the Query Optimizer may not be able to produce the optimum execution plan, and the query's performance may suffer. You can still manually create column statistics if you like, even when this option is turned off. There is really no down-side to using this option. The very first time that column statistics are created, there will be a short delay as they are created before the query runs the first time, causing the query to potentially take a little longer to run. But once the column statistics have been created, each time the same query runs, it should now run more efficiently than if the statistics did not exist in the first place. [7.0, 2000] ***** To provide the up-to-date statistics the query optimizer needs to make smart query optimization decisions, you will generally want to leave the "auto update statistics" database option on (the default setting). This helps to ensure that the optimizer statistics are valid, helping to ensure that queries are properly optimized when they are run. But this option is not a panacea. When a SQL Server database is under very heavy load, sometimes the auto update statistics feature can update the statistics on large tables at inappropriate times, such as the busiest time of the day. If you find that the auto update statistics feature is running at inappropriate times, you may want to turn it off, and then manually update the statistics (using UPDATE STATISTICS) when the database is under a less heavy load. But again, consider what will happen if you do turn off the auto update statistics feature. While turning this feature off may reduce some stress on your server by not running at inappropriate times of the day, it could also cause some of your queries not to be properly optimized, which could also put extra stress on your server during busy times. Like many optimization issues, you will probably need to experiment to see if turning this option on or off is more effective for your environment. But as a rule of thumb, if your server is not maxed out, then leaving this option on is probably the best decision. [7.0, 2000] ***** Many databases need to be shrunk periodically in order to free up disk space as older data is deleted from the database. But don't be tempted to use the "auto shrink" database option, as it can waste SQL Server resources unnecessarily. By default, the auto_shrink option is turned off, which means that the only way to free up empty space in a database is to do so manually. If you turn it on, SQL Server will then check every 30 minutes to see if it needs to shrink the database. Not only does this use up resources that could better be used elsewhere, it also can cause unexpected bottlenecks in your database when the auto_shrink process kicks in and does its work. If you need to shrink databases periodically, perform this step manually using the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands, or you can use the SQL Server Agent or create a Database Maintenance Plan to schedule regular file shrinking. [7.0, 2000] ***** This "auto close" database option is designed for use with the Desktop version of SQL Server 7.0 and 2000, not for the server versions. Because of this, it should not be turned on. What this option does is to close the database when the last database user disconnects from the database. When a connection requests access to the database, then the database has to be reopened, which takes time and overhead. The problem with this is that if the database is accessed frequently, which is the most likely case, then the database may have to close and reopened often, which puts a large performance drag on SQL Server and the applications or users making the connection. [7.0, 2000] ================================================================= **Sponsor's Message** ================================================================= VISUAL JOB MANAGEMENT FOR SQL SERVER IS HERE! Introducing SQL SENTRY, a new look at job management that brings you unprecedented scheduling, monitoring, alerting and reporting: SCHEDULING: Intuitive, Outlook-style calendar view of your job schedules. With 10-minute, hour, 4-hr, day and week views, job conflicts are clearly highlighted and easily resolved. MONITORING: Link Windows performance counters directly to jobs and guard against failures before they occur by knowing how jobs are impacting server performance. ALERTING: Robust notification capabilities for a wide variety of events, including min/max runtime, job conflicts, job queueing, and linked performance counter threshholds. REPORTING: 3-D runtime and performance charts provide unsurpassed analysis and resolution capabilities for job-related issues. For more information on SQL SENTRY, visit our website today: http://www.sqlSentry.net/ ================================================================= ================================================================= Winners of the January 2004 Forum Contest ================================================================= Here are the winners of the January 2004 forum contest: --frettmaestro --lazy_dba --holyterror_1 --negative --boldhead --pavankan --tdong --chappy --briant While the following people were ineligible for prizes this month, I also want to acknowledge them for their contributions to the forum in January 2004: --LuisMartin --satya --twan --FrankKalis --ChrisFretwell --bambola --gaurav_bindlish --joechang --Argyle I want to thank everyone above for their hard work and dedication to the SQL-Server-Performance.Com forum. If you haven't taken the time to check out the forum, you are missing a out on a valuable resource. Visit it today at: http://www.sql-server-performance.com/forum ================================================================= ================================================================= Win & Learn: FREE SOFTWARE in the February Forum Contest! ================================================================= SQL-Server-Performance.Com, along with 9 companies (see below) have teamed up to give away FREE SQL Server software to each of 9 WINNERS (one prize per winner) of the February 2004 Forum Posting Contest. Participating in the forum is not only a great way to win free software, but to learn a lot more about how to get the most out of SQL Server. --ApexSQL Code Clean ($149) from ApexSQL Software http://www.apexsql.com --NetworkSmart 2003 ($449) from LeadByte Software http://www.leadbyte.com --SQL Scribe Documentation Builder ($400) from A&G Software http://www.ag-software.com --DbNetGrid ($599) from DBNetLink http://www.dbnetgrid.com --myLittleAdmin ($490) from myLittleTools.net http://www.mylittletools.net --SQLZip ($500) from SQLZip Software http://www.sqlzip.com --mssqlXpress ($199) from XpressApps http://www.xpressapps.com RapTier Professional ($299) from SharpPower.Com http://www.sharppower.com Find Duplicates Wizard for SQL Server ($397) from Azlexica http://www.findduplicates.com The first place winner will get the first pick of the above free software, the second place winner will get the second pick, the third place winner will get the third pick, and so on. We have also changed the rules for the forum contest, making it even easier for participants to win. To find out about this contest, and how you can participate, please visit this webpage: http://www.sql-server-performance.com/ ================================================================= ================================================================= There are only three ways you could have received this e-mail, and that is to have subscribed to it, joined our forum, or to have received it from a friend who forwarded it to you. This is a 100% opt-in newsletter. To learn how to advertise in this publication, visit: http://www.sql-server-performance.com/sponsor_information.asp To subscribe to this newsletter, visit: http://www.sql-server-performance.com/subscribe_newsletter.asp To unsubscribe to this newsletter, or to change your e-mail address, click on this URL. ================================================================= Copyright 2004 Brad M. McGehee. All rights reserved. No part of this newsletter may be reproduced in whole or in part without written permission. =================================================================
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?1048810526-22004223193156343>