Optimize sql server queries with these advanced tuning techniques tech repu

Optimize sql server queries with these advanced tuning techniques tech repu

• 1.
  Optimize SQL Serverqueries with these advanced tuning techniques | TechRepublic ZDNet Asia SmartPlanet TechRepublic Log In Join TechRepublic FAQ Go Pro! s Blogs Downloads Newsletters Galleries Q&A Discussions News Research Library IT Management Development IT Support Data Center Networks Security Search Home / Blogs / The Enterprise Cloud Follow this blog: The Enterprise Cloud Optimize SQL Server queries with these advanced tuning techniques By Tim Chapman September 17, 2007, 11:57 AM PDT Takeaway: The best way to tune performance is to try to write your queries in a number of different ways and compare their reads and execution plans. Here are various techniques that you can use to try to optimize your database queries. Now that you know how to speed up your SQL Server database queries, you can start delving into some of the more advanced tuning options. Google Docs For Business The best way to tune performance is to try to write your queries in a number of different ways and Create & Upload Images, Tables, Equations, compare their reads and execution plans. Here are various techniques that you can use to try to Drawings, Links & More! optimize your database queries. www.google.com/apps 2TB Cloud Storage: $16 Appears on your computer like any disk drive. Use JOINs rather than subqueries Access files anywhere. www.livedrive.com If possible (and if it makes sense), I suggest using JOIN statements rather than subqueries to Free IPv6 Certification improve performance. When a subquery is used as criteria in a SELECT statement, the values Get started in minutes! Become an IPv6 Guru returned from the subquery are distinct. Returning a distinct list of values requires additional ipv6.he.net processing, which can slow down your queries. Use explicit transactions When data manipulation occurs in the database, the actions are written to the transaction log. If Keep Up with TechRepublic A CNET PROFESSIONAL BRAND your statements are executing many DML statements, it might be a good idea to place them inside of a transaction for performance purposes. Placing the statements inside of a transaction will prevent all of the statements from being written to the transaction log serially. For example, the Your Email following statement takes approximately seven seconds to execute on my laptop: Five Apps CREATE InsertTable ( Google in the Enterprise IDCol INT IDENTITY(1,1), ColVal INT Subscribe Today ) GO DECLARE @Counter INT SET @Counter = 1 Follow us however you choose! WHILE @Counter 15000 BEGIN INSERT INTO InsertTable(ColVal) SELECT DATEPART(ms, GETDATE()) http://www.techrepublic.com/blog/datacenter/optimize-sql-server-queries-with-these-advanced-tuning-techniques/179[08/29/2012 3:06:42 PM]
• 2.
  Optimize SQL Serverqueries with these advanced tuning techniques | TechRepublic SET @Counter = @Counter + 1 END Media Gallery If I wrap the INSERT statements inside of a transaction, it takes a little less than two seconds to execute. This is because the statements are inside of a transaction rather than committed to a transaction log until the transaction commits. This reduces the number of writes to the log. DECLARE @Counter INT SET @Counter = 1 BEGIN TRAN WHILE @Counter 15000 PHOTO GALLERY (1 of 15) BEGIN Curiosity's autonomous INSERT INTO InsertTable(ColVal) 'seven minutes of... SELECT DATEPART(ms, GETDATE()) More Galleries » SET @Counter = @Counter + 1 END COMMIT TRAN Note: I advise you to use this approach with care. If there are too many statements inside a transaction, it will increase the duration of the transaction, which increases the amount of time locks are held on the objects inside of the transaction. VIDEO (1 of 13) Use UNION ALL instead of UNION Cracking Open: HTC Titan II When you use the UNION clause to concatenate the results from two or more SELECT More Videos » statements, duplicate records are removed. This duplicate removal requires additional computing to accomplish. If you are not concerned that your results may include duplicate records, use the UNION ALL clause, which concatenates the full results from the SELECT statements. Hot Questions View All Use EXISTS when possible 3 SSL redirection When you need to check for the presence of certain conditions, it is usually faster to use the EXISTS function over COUNT(*). This is because COUNT(*) has to scan all records returned by the statement, while EXISTS will return a true value as soon as it finds a record that meets the Switching from a Job to a career in criteria. 3 the IT field: Need an IT pro's advice STATISTICS IO There are different ways to determine the best way to write your queries. Two of my favorite 2 windows 7 won't shutdown and methods are looking at the number of logical reads produced by the query and looking at graphical keeps switching on execution plans provided by SQL Server Management Studio. For determining the number of logical reads, you can turn the STATISTICS IO option ON. Consider this query: 2 can anyone suggest if any such software exist with similar SET STATISTICS IO ON functionality? SELECT * FROM SalesHistory The following is returned in the Messages window in SQL Server Management Studio: Ask a Question Table 'SalesHistory'. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. There are several bits of data returned by STATISTICS IO, but I am really only concerned with the Hot Discussions View All logical reads portion because it will tell me the number of pages read from the data cache. This is the most helpful to me because it will stay constant when I run the same query, which is important 221 Should developers be sued for because there are sometimes external factors that might vary the execution time of my queries, security holes? such as locking by other queries. When I’m tuning my queries, my goal is to get the number of logical reads as low as possible. 79 The sitting duck that is open Fewer logical reads typically leads to faster execution times. source Fine tuning 27 Five fast Windows desktop search utilities In a future article, I will look at how you can use the graphical execution plans created by SQL Server to fine tune your queries. 24 Five speedy Windows desktop Tim Chapman is a SQL Server database administrator and consultant who works for a bank in search apps Louisville, KY. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at Start a Discussion chapman.tim@gmail.com. —————————————————————————————– Get SQL tips in your inbox Blog Archive http://www.techrepublic.com/blog/datacenter/optimize-sql-server-queries-with-these-advanced-tuning-techniques/179[08/29/2012 3:06:42 PM]
• 3.
  Optimize SQL Serverqueries with these advanced tuning techniques | TechRepublic TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. August 2012 December 2011 Automatically subscribe today! July 2012 November 2011 Get IT Tips, news, and reviews delivered directly to your inbox by subscribing to TechRepublic’s free June 2012 October 2011 newsletters. May 2012 September 2011 April 2012 August 2011 March 2012 July 2011 February 2012 June 2011 January 2012 About Tim Chapman Full Bio Contact A primer on array-based and Rename Windows Server network-based replication 2003 domain controllers 16 Join the conversation! Add Your Opinion Comments Follow via: Staff Picks Top Rated Most Recent My Contacts See All Comments how to connect vb.net form to databace sqlserver. 0 mekashifraza 16th Jul 2009 Votes i have written all the required codes like connection ,data adapoter,dataset.but it not working View in thread Never ending update 0 JBNY Updated - 21st Oct 2007 Votes I ran this query in the Query Analyzer for 6 hours and then gave up. 29 million recs at 24GB. Update A Set Acctid = B.Acctid From B Where Substring(VendId,5,4) = B.VendId4 I did a link from Access and... Read Whole Comment + View in thread Damn right it's odd 0 Tony Hopkinson 20th Sep 2007 Votes One column look up by primary key closed set of integers 1 - 1000 ? The colleague was testing large batch queries in a payroll engine, I figured there were a lot of ways that might go different, so I... Read Whole Comment + View in thread See all comments http://www.techrepublic.com/blog/datacenter/optimize-sql-server-queries-with-these-advanced-tuning-techniques/179[08/29/2012 3:06:42 PM]
• 4.
  Optimize SQL Serverqueries with these advanced tuning techniques | TechRepublic Join the TechRepublic Community and join the conversation! Signing-up is free and quick, Do it now, we want to hear your opinion. Join Login Search Trending Topics Server virtualization Microsoft Excel SECURITY business analyst router SOFTWARE/WEB DEVELOPMENT IT OPERATIONS Microsoft Windows XP HARDWARE server it consultant Wireless LANs job Project management Featured TechRepublic Pro Downloads Explore Services Blogs About Us Downloads Membership Members Newsletters QA RSS Feeds DIscussions Site Map 500 Things You Guide to Mobile Quick Reference: Essential IT Forms Training Site Help Need To Know To Computing Support Linux Commands Store Feedback Succeed In Your IT Research Library FAQ career Photos Advertise Videos Reprint Policy Popular on CBS sites: US Open | PGA Championship | iPad | Video Game Reviews | Cell Phones © 2012 CBS Interactive. All rights reserved. Privacy Policy | Ad Choice | Terms of Use | Advertise | Jobs A ZDNet Web Site | Visit other CBS Interactive Sites: CBS Cares CBS Cares Go http://www.techrepublic.com/blog/datacenter/optimize-sql-server-queries-with-these-advanced-tuning-techniques/179[08/29/2012 3:06:42 PM]