jump to navigation

MySQL Series: Performance Engineering – Find What April 14, 2015

Posted by msrviking in MySQL, Performance, Performance tuning.
Tags: , , , , ,
add a comment

In the post MySQL Performance Engineering series – The Goal I had mentioned about the problem statement, and how I planned to go about it. I had series of areas that I need to attend to stabilize the performance and as an old adage I went by finding those low hanging fruits that were spoiling the backyard “the queries”.

Being a first timer on stabilizing the performance of MySQL system I was looking for a way out on, how would I know which queries are doing badly? As in SQL Server we have DMVs that capture the slow performing queries, or traces which profile the database system for a set events and columns (duration), there is something similar in MySQL world known as “Slow Query log”. Luckily I had this handy information from the Dev team who were receiving hourly notifications on which queries were running slow on a particular day.

Now to more details.

Slow Query Log, what is it?

Taking the text from The Slow Query Log page which says “A slow query log has those SQL statements that took longer than these ‘n’ seconds to fetch rows after examining ‘m’ number of rows of the table”. If I remember right, I believe we had set the value for long_query_time > 3s, and default value for min_examined_rows_limit. This would mean that mysqld would write to log file for any queries, stored procedures that need attention because they are exceeding the limit of execution of 3s.

There are few other settings too that could be configured before you enable logging of slow queries into the log, and could be pored through the above link of Slow Query Log.

A sample output of what the slow query log is here

/usr/sbin/mysqld, Version: 5.6.13-log (MySQL Community Server (GPL)). started with:

Tcp port: 3372 Unix socket: /db01/mysql/mysql.sock

Time Id Command Argument

SET timestamp=1390907486;

call sp_name (412,15000);

# Time: 140128 5:12:07

# User@Host: user[pwd] @ [xxx.xxx.xxx.xx] Id: 11963111

# Query_time: 54.035533 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 204534

Please read the above sample output for key information like

Call..-> is the stored procedure name that is called

Time -> is the time of execution of the stored procedure or a query

Query time -> the time taken in seconds for the stored procedure or query to complete

Rows examined -> number of rows looked through during the queries execution in the stored procedure

So what do you do with this raw data? I would think of running a utility that will read through the entire log and give me a summary of what is logged. Well, if I had to do this manually I would have had to use my brains stressed to a level of finding a right app, script, test, review (in cyclical pattern) and abstract the results out. I had been lucky here to and hadn’t spent that much time and effort. The next section will describe of what I did.

 

Raw data of Slow Query Log, what next?

In the above sub-section you would have noticed the output of the slow query log which is Raw, and information rich. Now, to get a decent report of the raw data I had to use the utility “mysqldumpslow” provided by MySQL. Mysqldumpslow is a perl (.pl) program that parses the slow query log and groups similar queries except if data values are different. More here on this nice little program. I didn’t use any additional parameters to parse through the log, and one could tweak the values of the options listed.

 

I had to do one more thing “Install a perl engine” before I could run the mysqldumpslow.pl in its location in my system. And then, I had to copy the raw slow query logs to the folder that standalone .pl file, and run the command mysqldumpslow through the command prompt in the path.

 

The output looks like this

 

Count: 2 Time=57.39s (114s) Lock=0.00s (0s) Rows=313.0 (626), user[pwd]@[xxx.xxx.xx.xx] || SELECT * FROM t WHERE col = ‘S’

Count: 617 Time=57.33s (35371s) Lock=0.12s (76s) Rows=1.0 (617), user[pwd]@4hosts || SELECT * FROM t1 WHERE col1 = ‘S’

Count: 713 Time=56.26s (40116s) Lock=0.72s (516s) Rows=1.0 (713), user[pwd]@4hosts || SELECT * FROM t4 WHERE col2 = ‘S’

Count: 3 Time=55.02s (165s) Lock=0.00s (0s) Rows=1.0 (3), user[pwd]@2hosts || select t1.col1 from t1 join(t2) where t1.col4 !=N and t1.col5=N and t1.col1=t2.col1 and t2.col2=’S’ and t1.col3=N

 

The above sample output has

Count -> number of times the query was called at that instance

Time in () -> total response time in seconds

Time not in () -> average response time in seconds

Lock in () -> total locking time in seconds

Lock not in () -> average response time in seconds

Rows in () -> rows examined

Rows not in () -> rows sent

User, pwd and hostname /host ip address -> speak for themselves

Query definition -> query that was executed with response time, lock time, rows examined, and sent

 

Please note the two | “||” after the query aren’t from the mysqldumpslow.pl. I had to modify the .pl file to include the || for bringing the line of output into single line, else I would have had a /n (enter character) after the hostname /ip. I wanted to pull all the data into one single line, and convert text to columns in excel for better readability and analysis.

 

Now the slow query log is formatted from the utility and it’s to be analyzed, and that wasn’t difficult as much figuring out on how to format for better reporting.

Analyze the Slow Query log

Here is the sample report I had to put in excel for better analysis

Query Profile
Calls Count Average Time (s) Total Time (s) Row Lock (s) # Rows Sent #Rows Examined Query Text
11391 43.47 495193 0.00 (2) 1 11391 StoredProcedure
3757 34.77 130632 0.45 (1695) 418 1570426 Query 1
1788 14.42 25779 0.29 (514) 1 1788 Query 2
1684 48.6 81849 0.04 (73) 1 1683 Query 3
1117 52.14 58244 0.12 (137) 1 1117 Query 4

Are there tools that could help you summarize all these, well I haven’t used any but yes there is one I have kept learning from here.

I decided to look for queries that had least calls count, and highest average or total response time. This was the onset of my performance engineering the MySQL systems for a single goal.

 

Happy reading.

Advertisements

A year and more gone by..no posts March 23, 2015

Posted by msrviking in Configuration, Data Integration, Design, Integration, MySQL, Oracle, Performance tuning.
Tags: , , , , , ,
add a comment

I alone know on how I have missed blogging on what all I learnt on MySQL and Oracle RDS over last 14 months, and how much I wished that what all I did was on SQL Server. Well, not always it is the way you want it to be.

There was such a learning over this period on the way things are done in MySQL, Oracle at design, development and performance engineering that I might have to spend several days blogging about. In next few weeks to months I will be spending time on variety of learnings starting from design, coding, through performance tuning to achieve concurrency and scalability. Although these topics will primarily be around MySQL, Oracle I might map on some of the done work to SQL Server features and implementations.

A brief preview on what would be the topics on, and possibly will be having its own sub-series in detail with mapping

MySQL:

  1. What are the physical design checklist
  2. What are best instance level configurations
  3. What are the optimal working configuration values of host hardware and OS
  4. What are the ways to optimize execution plans
  5. What are the coding best practices
  6. What are the ways to troubleshoot deadlocking problems

Oracle RDS:

  1. What are the best practices for coding in PL/SQL
  2. What are the best practices for designing and building integration DB
  3. What are the ways to optimize execution plans
  4. What are the ways to monitor for any performance bottlenecks
  5. What are the ways to achieve concurrency, scalability and performance
  6. What is that not do-able when compared to on-premise instances
  7. How to administer the instance

Happy reading.

What am I doing now-a-days over last few months..? January 30, 2014

Posted by msrviking in General, MySQL, Performance tuning.
Tags: , , , ,
add a comment

I had been busy doing stuff on SSIS for few months, then on MySQL from the last post I have put up in the blog. What is that I am doing on MySQL and writing a post on a SQL Server blogging site? Well, I am kind a trying to get hold on how MySQL works, while I am trying to stabilize the performance of system. Surely I am not a MySQL geek to look at OS, Hardware, MySQL configurations deeply, but with little knowledge I had and gaining as time is going by I am trying to troubleshoot performance of the queries, indexing, and partitioning tables. These are the few things that I have been trying to put in place even before I get on other levels of performance engineering.

A thread is already on if I could shard the database, introduce read-write splitting to provide a scale-out solution by using out of the box features like MySQL cluster or customizing by partitioning tables, sharding them into different nodes, read-write splitting using MemCache.

These are lot of thinking in terms of MySQL but then I don’t have such flexibility in SQL Server although there are some implementations that use read-write splitting, using load balancers at application level and not database. I am highlighting some of those that are not there in SQL Server, and available to be used at fullest in MySQL. But then there are many of those missing in MySQL which is so good to use and work out things in SQL Server.

Some of the ones that I am missing MySQL badly are

  • Profiler
  • Graphical execution plan
  • Indexes with include
  • Free and easy to use monitoring tools for OS, Hardware
  • Multiple query plan algorithms
  • Proper documentation (implementation or bugs)

This post is to share what I am seeing those top few things in MySQL and few things that I am missing when I think of SQL Server. It’s kind of missing or home-sick post I would say.

I will keep writing as and when I learn new thing and definitely put a comparison with SQL Server features.

Happy reading.

Data type and conversions December 7, 2012

Posted by msrviking in DBA Rant, Performance tuning, T-SQL.
Tags: , , , ,
add a comment

Today I downloaded SQL Server Data Type Conversion Chart from this location. I remember this chart very well and its available on BOL that would have been installed on your system or on MSDN online.

As soon as I saw the chart, it reminded me days where I used to explain to the development on its importance and always push the developers, leads to have a look on this topic in BOL. Today I am going to tell the teams whoever use SQL Server to download this and stick it around as long as they are on development side of database.

One of the points that I leave with the teams is

“Make sure you don’t have variables declared in your code that cannot match the data types that are designed in the tables. This would mean implicit conversions by query engine and when this happens it could lead to performance problems, deadlocks.”

There had been several situations where performance reviews and fixes ended by just change of variables data types in the T-SQL code. I don’t think I am going to stop sharing this point anytime, anywhere to developers forum whatsoever. There are experienced folks who do this mistake even today, and one can’t get rid of basic mistakes unless its part of practice. Practice has to be diligent, and has to be cultivated.

So if you see anyone writing a piece of code that could hit your performance benchmarks badly, please don’t hesitate to give this piece of advice again.

Let me know what you think.

Cheers and Enjoy!

Vote for the connect item March 19, 2010

Posted by msrviking in Performance tuning.
add a comment

Adam Machanic has opened an item in connect to have “missing join predicate” graphical representation to stand-out. Please vote for this, and this definitely makes sense, because even I get lost (as Adam did) while finding “What the heck is happening with the join?”. I voted, so how about you?

Here is where you want to vote.

Thanks!

OLTP database for reporting? March 3, 2010

Posted by msrviking in Performance tuning.
add a comment

There is a interesting tug-of-war going between the DBA’s and Architects on how to use OLTP database for reporting without impacting performance issue. Although the client has a set solution recommended by us earlier, but due to costs there is a consolidation of databases (not as databases data per se), instead having OLTP database and reporting database (copy or closest copy of OLTP) on the same system or different system.

Well, to justify on what I would think of I was searching in the web on what other DB guys think about all this. A very interesting article, which you will want to read is here. However, I shall definitely share what I would recommend.

Enjoy and Cheers!

Performance counters for performance November 18, 2009

Posted by msrviking in Performance tuning.
1 comment so far

I was reading through my blog roll today and saw an interesting blog by Buck Woody on how to use performance counters to monitor and assess performance of SQL Server system.

Here is the link and happy learning.

Cheers.

A quick update – Buck is writing a series of posts on his blog on the topic. I would suggest please visit the other parts of the series.

Timeout | Blocking | Wait types November 2, 2009

Posted by msrviking in Performance tuning.
add a comment

I am into a performance analysis and tuning assignment wherein I need to identify a “mysterious” performance issue. Sounds familiar by words, “Yes” but its really difficult if one doesn’t know where to start (the state I am in the beginning of the exercise).  Well, all these aside, but here is the problem statement given by the team “We have timeouts and deadlocks happening”.

With this small and simple one liner I started thinking like this,

– What is a time out? I had to get back to basics  for this and per definition which I love to read again and again is

“Before executing a query, SQL Server estimates how much memory it needs to run and tries to reserve this amount of memory from the buffer pool. If the reservation succeeds the query is executed immediately. If there is not enough memory readily available from the buffer pool, then the query is put into a queue with a timeout value, where the timeout value is guided by the query cost. The basic rule is: higher the estimated cost is, larger the time out value is. When the waiting time of this query exceeds the timeout value, a time out error is thrown and the query is removed from the queue.”

Well keeping in this in mind, I went hunting for memory, CPU configuration of the box.

– What is the configuration of the RAM, CPU and how is it doing?

64G RAM, and 8 core machine is having time out and deadlocks. Wow! Can’t believe it and so I decided to go to the box and check it myself on how it is doing. To my surprise all looks fine (RAM and CPU) running below the threshold levels. So what is that I am hunting for?

Over years of experience gave me a gut feeling which is usually very close to solution or at least to the  problem to start with, in such confused state of mind.  So my gut feeling was saying all looks fine so what is bad going on here.  A strong thought – maybe processes are getting blocked, and all these processes are accessing same tables through different t-sql batch of statements (procedures, functions).

– Does blocking cause timeouts?

This was the first question I had with series of questions like if timeouts are happening why isn’t evident in CPU /RAM utilization. What is going wrong and where? These questions started haunting me which led me to these interesting links which I am consuming (may be 10th time) to understand what should be my next question.

Here are the links for you guys

http://www.informit.com/articles/article.aspx?p=686168

http://sqlserverpedia.com/wiki/Wait_Types

I am curently using these links to arrive at some information for analysis and tune the damn queries causing performance issue through blocking.

I shall keep you guys posted on how I progress, but remember yes blocking & wait type are related (although I didn’t mention about wait types explicitly in the post) and yes of course you have time outs because of blocking which I shall confirm it after this.

Maybe you guys know all these, and even I do but thought of blogging as my thoughts were flowing else I need to spend “regurgetating” you see ;).

Trace skipped records – SQL Server profiler October 28, 2009

Posted by msrviking in Performance tuning.
add a comment

Recently I was working on performance analysis of a “mystery monster” stored procedure in one of the projects at my shop. I was working on this stored procedure in the usual way I try to pick up pain points of stored procedurs /sql statements. And I decided to run SQL Server profiler to profile the performance of the stored procedure. All was done and profiler ran fine and I could see lot of information captured although not overwhelming. I took a deep breath to understand 2000 lines of profiled data, and as I was scanning through I saw something like this and have never seen it before.

Here is the snapshot which I had in the profiler,

 

Trace Skipped records

Profiler results

 

I thought this is something new which I haven’t seen before and took it as learning (partly ignorance too 😦 ).  But, it is so coincident that someone, somewhere had the same problem and this is what I appreciate about sharing information across the globe.  That someone is ScaryDBA  who had the same issue, but he went and dug around on to see what is this all about?  Well here is what he had to say in his blog post – Snags with Profiler GUI.

Learnings – Don’t be ignorant like me and dig around if you find something new else it will be half-knowledge.

Thanks to ScaryDBA for his thoughts!

Enjoy.

 

Performance analysis methodology September 25, 2009

Posted by msrviking in Performance tuning.
add a comment

Hello Guys,

Few months ago I had worked on performance analysis of database that needs to be upgraded from SQL Server 2000 to SQL Server 2008. Before doing the analysis, and providing recomemndations I thought I will document the methodology and share with the project team. As an effect of this thought I had prepare this methodology.

Start of the document:

Performance Issues – Database upgrade from SQL Server 2000 (32 bit) to SQL Server 2008 (64 bit)

Overall databases performance

Since, the source databases are moved from the existing system (SQL Server 2000 – 32 bit environment) to the destination system (SQL Server 2008 – 64 bit environment), the overall database performance would improve for the reasons that SQL Server 2008 has improvised database engine and the instance would be hosted on the 64 bit environment.

However there could be stored procedures, user defined functions, triggers and other database objects which may degrade performance after the upgrade because of the following reasons.

–          Query engine interpretation of execution plans in SQL Server 2008 is different to that of SQL Server 2000.

–          Statistics on columns and indexes will not be affective for query optimizer to prepare optimal plans.

–          Row counts or page counts will be inconsistent and /or incorrect.

To ensure that there is no performance issues due to upgrade, baseline performance will be identified for stored procedures, user defined functions, and also post-upgrade activities will be considered post the production deployment.

Poorly performing existing stored procedures

The identified poor performing stored procedures could have improvements in performance because of the improvised database engine features of SQL Server 2008 and higher processing power (64 bit) on which SQL Server will be hosted.

But the performance could be still not per existing environment because of the following reasons:

  • Poorly written queries.
  • Highly complex T-SQL logic.

Assessment

Overall databases performance

The process of measuring the current performance will be to obtain a performance baseline, and this baseline will provide statistics of current usage pattern of the stored procedures, user defined functions and will also give a reference for the future statistics.

  1. The performance baseline information will be collected after running traces. The traces will have to be created in the identified baseline environment, along with trace dependent physical tables and stored procedures for the traces to run. Essentially in short these will be the components for the traces to be run
  • Stored Procedures which will create, start, stop and clear traces in the SQL Server instance
  • Physical Tables will consist of trace based information columns like events, data columns.
  1. The traces will be run on SQL Server 2000 in all databases before migration and on SQL Server 2008 after migration. The few options on where to run the traces could be run.
  • Running trace in production environment. This is the optimal environment, for the reason that the traces will return actual figures of what is happening in the production environment on the existing stored procedures, functions, and any other DML operations.
  • Running trace in pre-production, UAT or any environment which is closest to production environment. The closest to production environment means that the boxes should be similar with that of production boxes in terms of hardware configuration. Although there will not be enough load on the server to sense how the sp’s are doing while under load, but at least there will be good readings (Duration, CPU, RAM usage) that may be close to production box.

In order to simulate production environment load, have few users could access and test the application with multiple flows and scenarios. As an alternative to simulate load, a single flow /scenario could be executed to gather enough information for the trace. To compare the performance measurements after migration the same flow /scenarios should be followed.

To baseline pre-migration and post-migration performance information, the key parameters that will be captured for the events (SP start and SP end, SQL start and SQL end) would be

  • Database Name
  • Object Name
  • Duration (ms)
  • CPU (ms)
  • Logical Reads
  • Physical Reads

This information will be gathered in trace file format (*.trc) and saved into a folders. After the trace files are captured these will be saved into physical tables, and scripts will be run on the trace tables in order to retrieve the consolidated numbers on how many sp’s, function’s, trigger’s are called through the application, and also performance indicators (above mentioned parameters).

Poorly performing stored procedures

To identify the pain areas and to improve performance in these stored procedures, below information would be required,

–       Functionality of the stored procedures and how these sp’s are being used in the application.

–       The current issues that are causing performance bottlenecks.

–       SQL Server 2008 new features or the best practices that could be considered to fit into these sp’s.

And to gather the above information the below activities will be carried out.

–          Understand the functionality of the stored procedures.

–          Analyze and evaluate the current performance of the sp’s in SQL Server 2000 environment.

–          Identify the pain areas in the stored procedures.

–          Fix the pain areas with new features of SQL Server 2008 and with best practices.

Analysis and Findings

Overall databases performance

The below steps will be executed on the trace results that are gathered from the production environment to arrive at a baseline performance.

–       Load the trace files data of each database into physical tables.

–       Extract relevant SQL statements from “textdata” column of the trace table based on the event class 41, and 12. These events represent T-SQL event class and the events are SQL Batch Completed, SQL Stmt Completed.

–       Extract the object names from the exec statements (extracted from the above step).

–       For each object name the table will be queried on the “textdata” column to see where has the sp been called with the pattern “exec <objectname>” for the event class 41, and 12.

–       The event classes 41, 12 have very useful information like duration (ms), logical reads, physical writes and CPU (ms) for each exec statement.

The trace /profiler results may have captured only the sp’s that were being called by the application during the tracing period, and may not be all the n (total sp’s) in the database.

Poorly performing stored procedures

After understanding the functionality, analyzing and evaluating the stored procedures a list of few best practices and new features will be arrived at, and which could be implemented in the stored procedures for performance gains.

The below list of best practices and new features could be used for implementation.

Best Practices:

–          Use object owner against the objects used in the sp.

–          Temp tables to be replaced with table variables wherever applicable.

–          Use sp_executesql, and parameterized queries to avoid excessive recompilation.

–          Replace cursor with SET based operations.

–          Avoid use of SELECT INTO statements instead use INSERT INTO.

New Features of SQL Server 2008:

The below list gives description of the new features of SQL Server 2008, and where could these be used.

–          Common Table Expressions (CTE):

  1. As replacement for derived table.
  2. Replace table variables /temp tables.
  3. Avoid repeat of code /same query again.
  4. Hierarchical queries (recursive queries – calling query in a query).

–          Table Valued Parameters (TVP):

With TVP data can be passed around as a single variable. What this means is that if multiple records are saved, this can be done very easily in one procedure call.

This also can be done using an XML column or a delimited list, but the difference with a Table Valued Parameter is that a typed row set which can be used directly in an INSERT, SELECT, UPDATE statement, and any other statement that can take a row set. This means no shredding of XML or having a function to split delimited string. This essentially reduces the code, and gives flexibility of using typed data, that would mean reduced calls to the database from the DAL increasing the performance. However as it is indicative that there will be performance improvements but may not be possible to implement for the reason there will be changes in the stored procedures and the code calling the sp’s (T-SQL or the C#).

–          CONVERT function:

The convert function helps in converting binary data to string characters in hex format directly.

–           GROUPING SETS:

  1. The Grouping Sets feature is really helpful when you want to generate a set of aggregate results and at the same time you want to group by varying columns.
  2. It is much easier to maintain and provides better performance when compared to running different queries against the same data and then finally performing a UNION ALL to get the desired results.
  3. It provides better performance as it is executes once against the data source.
  4. It is much easier to program and use Grouping Sets than writing multiple select statements.

–          Transact-SQL row constructors:

This feature doesn’t give performance improvement, but helps in code size reduction which would essentially mean the execution plan will be slim and easy for the query engine to interpret.

–          Compound Assignment Operator:

This feature essentially reduces the code content in a batch of T-SQL statements.

–           Sparse columns:

The sparse column is an ordinary column just like other columns but it reduces the storage requirement for null values. A nullable column can be made as sparse column by adding the SPARSE keyword when the table is created or altered. Once the column is a SPARSE column SQL Server will not allocate space for null values. Note that in using this feature it adds an overhead for data retrieval of non-null values. Therefore it is needed to carefully apply this feature for columns by calculating the space that can be saved. It is recommended to make the column a SPARSE column only if the space that could be saved is at least 20 to 40 percent.

–          Filtered indexes:

A Filtered Index is an optimized form of non clustered index. It is basically useful to cover those queries which return a small percentage of data from a well defined subset of data within a table.

Filtered Indexes is one of the greatest performance improvement introduced in SQL server 2008. A Filtered Index allows us to create a filter to index a subset of rows within a table. i.e., non clustered indexes can be created with a WHERE clause.

A very well designed filtered index will help improve query performance on very large tables; this will also generate a better execution plan as it will be much smaller than the full table non clustered index. It is more accurate than a full table non clustered index because it will only cover those rows available in the filtered index WHERE clause.

A Filtered index will help reduce the index maintenance costs as it is smaller and is maintained only when Data Manipulation Language (DML) statements affect the data in the index. It is better to have large number of filtered index, especially in scenarios when the data is known on which filtered index is created is changed very less frequently.  Similarly, if a filtered index contains only the frequently affected data, the smaller size of the filtered index reduces the cost of updating the statistics.

Another major advantage of creating a Filtered Index is that it will reduce the disk storage space for non clustered indexes when a full table index is not required. A full table scan can be replaced with multiple filtered indexes without significantly increasing the disk storage space for the indexes.

But to implement all these one needs to essentially understand the functionality of the column and indexes along with the execution plan.

–          MERGE statement:

The MERGE statement internally works as an individual insert, update and delete statement within a single Merge statement. The SOURCE and the TARGET table or query which should be joined together should be specified. Within the MERGE statement specify the type of the data modification that needs to be performed when the records between the source and target are matched and what actions needs to be performed when they are not matched. With the introduction of MERGE statement the complex TSQL codes which was used earlier to do checks for the existence or inexistence of data within the data warehouse can be replaced with single Merge statement. The use of Merge statement will also improve the query performance because MERGE Statement runs through data only once in the database which also improves performance.

–          Composable DML:

The OUTPUT operator works much like a “local trigger” on the current statement. The drawback is that there is no way to filter the returned result set directly. The OUTPUT data has to be inserted in a staging table and work from there.

With composable DML a statement of UPDATE, DELETE and even MERGE as a data source for your query can be used. This doesn’t give any performance improvements but will be useful for sp’s that use the functionality of OUTPUT clause, and this would be an advance feature.

–          Table hints:

The FORCESEEK table hint forces the query optimizer to use only an index seek operation as the access path to the data in the table or view referenced in the query. You can use this table hint to override the default plan chosen by the query optimizer to avoid performance issues caused by an inefficient query plan. For example, if a plan contains table or index scan operators, and the corresponding tables cause a high number of reads during the execution of the query, as observed in the STATISTICS IO output, forcing an index seek operation may yield better query performance. This is especially true when inaccurate cardinality or cost estimations cause the optimizer to favor scan operations at plan compilation time.

SQL Server query optimizer typically selects the best execution plan for a query, we recommend using hints only as a last resort by experienced developers and database administrators. Moreover this would involve analyzing the sp for statement level performance and judging if the FORCESEEK hint helps the sp or not will involve time and effort.

–          FOR XML PATH:

This feature doesn’t give any significant performance gain but would reduce the complexity of the code which uses FOR XML EXPLICIT to return XML data.

Fixing and Re-evaluation

Overall databases performance

The above information gives on how much time each sp is taking, how many CPU cycles is the sp taking, what are the logical reads, what are the physical writes. With this data as baseline per sp on each database, the performance will be evaluated of the same sp, by executing same exec statement (as it was captured from the production) on 64 bit SQL Server 2008 Enterprise (testing environment) against each database.

Any differences in performance based on the parameters of duration /logical reads would lead to evaluate on why there are performance difference with the issue based sp(s), and changes will be proposed to get the performance in line or get better in comparison with the existing production system. After the first iteration of proposed changes is implemented the stored procedures will be evaluated again for performance.

Poorly performing stored procedures

After the best practices and new features are implemented in the stored procedures, the sp’s will be evaluated for performance on the 64 bit SQL Server 2008 Enterprise (testing environment). In case there are any performance degradations in the sp’s due to changes, these will be analyzed for performance analysis and changes will be made followed by re-evaluation until performance reaches to acceptable levels.

End of the document:

Let me know what you guys think about all these!

Enjoy!