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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

about sql tuning

Posted By:      Posted Date: September 28, 2010    Points: 0   Category :Sql Server

i have selected around two thouands record and the retrieveal time in database is around 8 seconds.

but when i retrieve the record in exe (window application with c#) it seems wait for a long time to retrieve, how can i turning what the problem happened?

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.

Speed SQL: Tuning Your Database Calls with Tier Interaction Profiling


Tier interaction profiling (TIP) is a feature of the Visual Studio profiling tools that measures the duration of synchronous calls to ADO.NET-compliant data stores. We'll show you how TIP works and provide some best practices for using TIP to diagnose performance problems in your apps.

Mark Friedman

MSDN Magazine August 2010

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

Multiprocessor Optimizations: Fine-Tuning Concurrent Access to Large Data Collections


Application performance involves more than just speed. In a Web server environment, top performance also means ensuring that the maximum numbers of users can be served concurrently. This can be accomplished through efficient use of multiprocessor machines and thread management. This article presents techniques that can solve a number of concurrency problems. One approach, using thread management, controls access to a database on a per-thread basis, which protects the integrity of the data. In the article, reusable thread classes are built and presented. The classes are then tested and their performance in a live environment is examined.

Ian Emmons

MSDN Magazine August 2001

2008 R2 Tuning Advisor reports "Error: Exception encountered(DTAEngine)"

I am encountering an error when I try to use the SQL 2008 R2 Database Engine Tuning Advisor. I create a very simple workload (either by right-clicking a query in SSMS or by loading a file), and click Start Analysis.  In the "Consuming Workload" step DTA aborts, and in the message column displays "Error: Exception Encountered(DTAEngine)". When I click on the error message, I get a dialog box with a title of DTAEngine, and a message "Error: Exception encountered". This is a clean test environment (SQL 2008 R2 Data Center Edition 64-bit, on Windows Server 2008 R2 64-bit, in a VM) against a local database. Any ideas about what the problem is or how to resolve it? See below for detailed version information: 10.50.1600.1 RTM Data Center Edition (64-bit) Database Engine Tuning Advisor      10.50.1600.1 Microsoft Data Access Components (MDAC)      6.1.7600.16385 Microsoft MSXML      3.0 6.0 Microsoft Internet Explorer      8.0.7600.16385 Microsoft .NET Framework      2.0.50727.4952 Operating System      6.1.7600    

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

What are the steps that any one should take for performance tuning in the case of sql-queries ?

Failed to initialize MSDB database for tuning (exit code: -1073741819). (DTAClient)

Hello,This is the first time I am using this forum. I am stuck and need some help. I received this error message both on SQL 2005 and 2008. I am currently using SQL 2008 DEV for testing purposes and I am experiencing this error message when trying to connect to Database Engine Tuning Advisor. I am using the SA account so I should not have any security issues. Also this is a fresh install of SQL 2008 DEV on a fresh install of Windows XP Pro with all SP. Can some please help.Error:"Failed to initialize MSDB database for tuning (exit code: -1073741819). (DTAClient)"=================================== Failed to open a new connection. =================================== Failed to initialize MSDB database for tuning (exit code: -1073741819). (DTAClient) ------------------------------Program Location:    at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeTuningParametersDatabase()   at Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeConnection()   at Microsoft.SqlServer.Management.DTA.Client.TuningServer.Connect()   at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServerInternal(SqlConnectionInfo connectionInfo, SqlConnection connection)   at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServer(SqlConnectionInfo connectionInfo, SqlConnection connection)   at Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.On

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

MDX Query tuning with subcubes usage

Hello, I've an SSAS DB with 8+ dimensions among which 4 are regular dimensions and 4 are parent-child dimensions. The problem is I've an MDX query whose execution is taking 45 seconds of time. Following is my MDX query : Select Non Empty(Hierarchize({{DrillDownLevel({{[PCDim1].&[716]}})}})) on Rows         ,Non Empty(Hierarchize({{Descendants({[TimeDim1].&[5045],[Time].&[TimeDim1]},[Time].[Month],SELF)}})) on Columns From MyCube where ({[PCDim2].&[101],[PCDim2].&[102]} *         ([PCDim3].&[1],[PCDim4].&[1],[RegDim1].&[104]),[RegDim2].&[1],[RegDim3].&[14],[Measures].[MTD]) I tried to speedup the query execution by using the subcubes. Following is the altered query:   Select Non Empty(Hierarchize({{DrillDownLevel({{[PCDim1].&[716]}})}})) on Rows         ,Non Empty(Hierarchize({{Descendants({[TimeDim1].&[5045],[Time].&[TimeDim1]},[Time].[Month],SELF)}})) on Columns From ( Select {[PCDim2].&[101],[PCDim2].&[102]} on Columns From MyCube ) where (([PCDim3].&[1],[PCDim4].&[1],[RegDim1].&[104]),[RegDim2].&[1],[RegDim3].&[14],[Measures].[MTD]) surprisingly this took more time to execute when compared with the earlier query execution time. I've read that using subcubes in MDX query would speedup the query

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.

General Best Practice for tuning SQL Server 2008


on physical and or virutal machine, such as considerations regarding disk I/O, CPU, RAM, Network etc.

partitioned view performance tuning question


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

create view Everything
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

Database Tuning Advisor, Stored Procedure with datetime parameters, syntax error


I receive these error(s) in the Tuning Log. I have several stored procedures and they all use a datetime parameter.

E000    exec <some sproc name> ''2009-01-01 00:00:00:000'',''2010-09-30 00:00:00:000'',N'',N'',NULL,NULL,NULL,NULL,NULL,NULL         1    [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2009'.

I am using SQL 2005. Any suggestions?

Thanks, Reece

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