.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

Can any one give me guide line in performance tuning of sql queries

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
 
What are the steps that any one should take for performance tuning in the case of sql-queries ?


View Complete Post


More Related Resource Links

Performance tuning tips for database developers

  
Performance tuning is not easy and there aren't any silver bullets, but you can go a surprisingly long way with a few basic guidelines.

In theory, performance tuning is done by a DBA. But in practice, the DBA is not going to have time to scrutinize every change made to a stored procedure. Learning to do basic tuning might save you from reworking code late in the game.

Below is my list of the top 15 things I believe developers should do as a matter of course to tune performance when coding. These are the low hanging fruit of SQL Server performance - they are easy to do and often have a substantial impact. Doing these won't guarantee lightening fast performance, but it won't be slow either.

Performance Tuning on DirectShow Camera Application

  
Windows CE 6.0 supports DirectShow® components to develop a rich camera application. A minimum requirement of the camera application is to show preview, capturing the still images which is stored as a compressed file (JPG) format, capturing/multiplexing the video with or without audio i.e. stored as a compressed file (WMV) format.

Windows CE 6.0 provides sample applications for showing preview, capturing still and video using DirectShow® components. However these sample applications doesn't cover all the aspects to develop a commercial application. A commercial camera application at least satisfies the following performance criteria.

Thread Diagnostics: Performance Tuning with The Concurrency Visualizer in Visual Studio 2010

  

Understanding performance bottlenecks in multithreaded apps used to require significant instrumentation and analysis by expert developers. The Concurrency Visualizer in Visual Studio 2010 significantly reduces the burden of parallel performance analysis.

Hazim Shafi

MSDN Magazine March 2010


Event Tracing: Improve Debugging And Performance Tuning With ETW

  

Event Tracing for Windows (ETW) provides general-purpose, high-speed tracing of events raised by both user-mode applications and kernel-mode device drivers. Learn how ETW can improve your development and debugging work.

Dr. Insung Park and Ricky Buch

MSDN Magazine April 2007


.NET Code Tuning: Make Your Apps Fly with the New Enterprise Performance Tool

  

Because the common language runtime (CLR) is a black box, it's pretty hard to divine what's going on when you want to track down performance problems. Microsoft will be delivering a brand new profiler, the Enterprise Performance Tool (EPT), as part of Visual Studio 2005 Team Developer Edition that's ideal for use on a production system because it offers some very lightweight means of collecting performance data. Here John Robbins takes you on a tour.

John Robbins

MSDN Magazine December 2004


wcf performance tuning questions

  
We have an IIS 7.5 WAS hosted (net.tcp) internal WCF Service.  It is running on a Win2008 X64 R2 server with 8 cores (16 with hyperthreading) and 16gb RAM. The App Pool is set to 16 max worker processes.  We are trying to tune the client / service so that we can run as many worker processes as possible and keep the server as close to 100% CPU utilization as possible.   The client is coded for for 17 concurrent threads / WCF service requests (using .NET 4.0 TPL library).   However, I believe there is some bottleneck going on as the server CPU's never get above 40% utilization and the number of worker processes stays fairly low.  What is the best way to determine where the bottleneck is?  How do you determine if the bottle neck is client side or server side?   What perf counters should I be looking at client side?  What perf counters should I be looking at server side?  Is there a more efficient binding than Net.Tcp?   thanks

Performance Tuning

  
Hi all,In my application , I am using many number of farpoint spread control. So the size of the page will be large.So the performance was very low. I have improved that by using update panels and some clientside scripts.while i am going through some articles on net, i found some suggestions as1. To use string.Empty  instead of ""2. To use StringBuilder instead of string.so i tried like ,        string str = string.Empty;        for (int i = 1; i <= 100000; i++)        {            str += string.Empty;        }now i am getting End Raise PostBackEvent 0.00284593221345047 0.001111 when i use,StringBuilder sb = new StringBuilder(string.Empty);        for (int i = 1; i <= 100000; i++)        {            sb.Append(string.Empty);        }the trace was End Raise PostBackEvent 0.00284593221345047 0.002418 .can you please tell me the difference.Also please suggest me some techniques to improve the performance.Thanks

Performance tuning > two tables with strings > best query to join them

  

I have two subqueries which build a string (varchar(200)), first subquery creates 200000 records and second 50000 records, how can I write a select statement to get records from subquery first with string existing in second subquery records.

Will sorting both the sub query results be helpful? Kindly advice for best performing select statement.


Performance Queries

  

Hello!


I am developing an application for queries and reporting. The problem is that access to data is through an old database fox pro, via the network, the system still works for the company, the application is for queries and reports that will be available on the Local network.

I wonder if I can increase performance if I copy the database locally before starting the day or in a specific time of day, to make access faster and if you need to update the data then you would copy it again.

As I say, is only for reports that do not require that the information is at the last minute.

Some queries take longer to be executed.

What you will do? of course, Better would be a new systems jejej, but they won't.

Regards!


Slow performance with SQL Server 2005 executing parameterized queries with LIKE and an aggregate com

  

We have a slow-performing query and, after some analysis, have narrowed the cause down to using parametrized queries in conjunction with LIKE and aggregate comparisons used in the WHERE clause.  If we use a non-parametrized query (but keep the WHERE clause the same) the query performs much faster and, in fact, executes a different plan.  Using OPTIMIZE FOR UNKNOWN is not an option for us as we are using SQL Server 2005.  Obviously, we'd like to use parametrized queries to prevent SQL injection but the slow perf is unacceptable.  We need the LIKE to support wildcard scenarios.  If we need to we'll use dynamic SQL (scrubbed as much as we can) in lieu of parametrized queries.  We're wondering, however, is there another option that would give us the perf without sacrificing safety?  I can provide a sample database, query, plans, etc. if necessary...I'm assuming SQL Server generates a less efficient plan with the parametrized query as it can't make any assumptions about the parameter values...

Mike Bouck


Slow performance with SQL Server 2005 executing parameterized queries with LIKE and an aggregate com

  

We have a slow-performing query and, after some analysis, have narrowed the cause down to using parametrized queries in conjunction with LIKE and aggregate comparisons used in the WHERE clause.  If we use a non-parametrized query (but keep the WHERE clause the same) the query performs much faster and, in fact, executes a different plan.  Using OPTIMIZE FOR UNKNOWN is not an option for us as we are using SQL Server 2005.  Obviously, we'd like to use parametrized queries to prevent SQL injection but the slow perf is unacceptable.  We need the LIKE to support wildcard scenarios.  If we need to we'll use dynamic SQL (scrubbed as much as we can) in lieu of parametrized queries.  We're wondering, however, is there another option that would give us the perf without sacrificing safety?  I can provide a sample database, query, plans, etc. if necessary...I'm assuming SQL Server generates a less efficient plan with the parametrized query as it can't make any assumptions about the parameter values...

Mike Bouck


partitioned view performance tuning question

  

There are 20 tables with the same schema. And a view

create view Everything
as 
select 1 as area, pk_col1, pk_col2, col1, col2, ... from T1
union all 
select 2 as area, pk_col1, pk_col2, col1, col2, ... from T2
union all 
select 3 as area, pk_col1, pk_col2, col1, col2, ... from T3
union all 
select 4 as area, pk_col1, pk_col2, col1, col2, ... from T4
union all 
select 5 as area, pk_col1, pk_col2, col1, col2, ... from T5
......

However, the foll

performance tuning of stored procedures

  
How do i do performance tuning of stored procedures?
I was browsing the internet and found this link, http://www.sql-server-performance.com/tips/stored_procedures_p1.aspx

but is there any other way like a step by step procedure that i need to follow in order to fine tune the procs?

I heard that we can use sql profiler and database engine tuning advisor, but i don't know how to use those tools for performance tuning of stored procedures?

Would be eagerly waiting for your replies :)

Thanks

Performance Tuning

  

1)      What meant by Performance Tuning, how can we do the performance tuning on stored procedures and tell some steps to do the performance tuning?


Doubts on performance tuning

  

Hi, 

Caught up with few Issues, Advice is highly appreciated:

 We are trying to improve the performance of our query by adding some indexes amd eliminating some unwanted stuff. When I run the old and the new query in o together and check the execution plan, I could see Cost relative to batch, the old query is higher then the new query,I think that is a good sign but what I want to know is how true it is. The reason for asking is I have read it is only estimate and not neccessarily be true. 

 And also , While testing I would like to clear the server cache, I am using dbcc dropcleanbuffers command for that, Does that clear the server cache or is there any other Command?

Is it good to have Clustered index on identity field, it also happens to be the PK of the table.Are there any disadvantages in making identity field as PK and have clustered index on it?

 

While doing Transactional Replication, if we happen to add a new table at publisher, does that not replicated on to the subscriber unless a snapshot is generated again, if not, is there anyway we can generate the snapshot only for that table and replicate it,because I do not want to generate the snapshot for the entire DB.

thanks,



Very important SQL performance queries - index building

  

Hello i need several queries.

1- auto index suggestion query. dont tell me sql server profiler since it always fails because my sql server processes about 10000 query per second maybe more

2- performance degreading index finder query

3- duplicate index finder query

4- fragmantation rebuild query

 

thank you


Browser based Pokemon MMORPG Pokemon Craft Developer Used asp.net 4.0 routing at it's Pokedex

ASP.NET Best Practices for High Performance Applications

  
This article lists the techniques that you can use to maximize the performance of your ASP.NET applications. It provides common issues, design guidelines, and coding tips to build optimal and robust solutions.
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend