.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

Steps to performance tune multiple databases with multiple stored procedures

Posted By:      Posted Date: September 18, 2010    Points: 0   Category :Sql Server
I have a sql2008R2 server with approximately 10 databases and 1400 stored procedures that will eventually support a website.  I currently have 3 sql2008 machines doing the same in production.  The goal of the new server is to remove all non-clustered indexes and performance tune all the stored procedures using the sql tuning adviser and then compare the performance to what we have in production.  I have been dragging my feet on this project as I dont really know where to start as many of the procs exec other procs and access many database objects.  I feel that there should be a specific order in how I start this project and I am looking for some advice from other users that may have had a similar project.   Thanks in advance.

View Complete Post

More Related Resource Links

Multiple databases on same instance - performance hit?

Hi I am not a SQL person to please excuse me if my terminology is wrong :) We are running SQL 2005 at the moment on Windows 2003 SP2 servers. We have multiple SQL servers spread around in each of our data centres. We have an application we're going to be bringing in that needs it's own SQL database. We need to decide whether we build a dedicated SQL server for this or just create a new database on an exisiting SQL instance. The advantages of the former are that we don't max out any existing servers and affect the performance of other databases, whereas with the latter we save the cost of having to build a new server. For those of you that use SQL heavily, what's the general way of doing things? I know that SQL is memory, disk intensive etc so not sure.

Can't SSRS 2008 handle stored procedures which return multiple tables?


I tried it and it only returns the first table.

Any suggestion?

Thank you,


Performance r/w xml and dealing with multiple access.



I've working on a chat type project at the moment. I was originally using a DB but decided to move to XML which has improved speed a lot and also made the control more portable. I have two related issues that I was hoping you guys could pass comment on.

The first is dealing with multiple users either reading or trying to write to the xml file when someone else already has it open. I've had to be an Exception loop around the write process to 'queue' the writes from different users. This means that sometimes there is now an issue with speed of response.

The second issue is to do with the speed at which my code read and writes xml. Speeding it up would help my app generally and but would also help with issue one.

Here is my code. Just so you are aware, when someone makes a post it does Savemessage and then Getmessages. Getmessages also runs every 3 seconds regardless:

public List<Chat.Message> Getmessages(int numberOfMessages, int lastMsgID)
        List<Chat.Message> res = new List<Chat.Message>();

        DataSet xmlDB = new DataSet("xmlMSG");        

        if (xmlDB.Tables.Count > 0)
            // I only want a maximum of the last

how to write a stored proc to prevent multiple users in uploading the reports at a time? plsss help



I wanted to know how to prevent more than one user from uploading the report at a time .I am new to .net ,plss help with the code that would be great.Details are below:

This is a Windos based app written in c# . When User A  clicks Upload option on  one server  from the menu  to upload the files  and at the same time when User B clicks Upload option on different server , User B should be alerted a message saying "User A's uploading is in progress,pls wait" . How to achieve this, plsss help with the code.. I am thinking this logic should be kept in a stored proc, How do I write that proc? plss help


Thanks in advance!

Running a single SQL update query on multiple foxpro databases

Hi, Env : SSIS 2008 My requirement is to run a update SQL statement on multiple foxpro databases. The fox database paths are available in a SQL server table.  I've created a for each loop control and assigned the database path to a variable named dbpath. Within the loop, I added an "execute sql task" control. Now what properties are to be set to get the requirement done. Appreciate any help. Regards. BH  Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

MOSS 2007: list column that lookup to multiple documents stored in doc library

Hi All, I'm trying to understand how to customise MOSS to achieve the following: I need to lookup from a list to multiple documents stored in a documetn library. Example: - List item 1 --------> Lookup to Doc 2                   --------> Lookup to Doc 5 - List item 2 --------> Lookup to Doc 3                   --------> Lookup to Doc 5                   --------> Lookup to Doc 8 - List item 3 --------> Lookup to Doc 1 How can I achieve this? is it possible that in the lookup I have a "search" option? Thanks all Vit

ASP.NET Web Application connecting to multiple databases

Hi Experts,I am supposed to develop an enterprise-class ASP.NET Web Application that connects to multiple databases. For example when the web form loads, I should populate different ASP.NET controls (GridView, DataPager, TextBox, DropdownList etc) from multiple databases (SQL Server, Oracle, DB2, SAP Server etc) and also, edit the data in the controls and save the data to the respective databases on button submit.Ideally, what should be approach? Should I use Web Service or WCF for data integration, if so is there a limit on the size of the data?What should be the architecture considering the application should be scalable and easy to maintain?Pardon my ignorance.Thanks

Report using Stored Proc calling multiple Stored Procs


Hi guys,

I am new to SSRS. I have to create a report from a Stored Proc. Thi Stored Proc excepts just one parameter 'ErecNo'. This parameter is passed to fetch the columns from table1. This result set contains OrderID. The OrderId is further passes to 5 SP's in to fetch 5 more result sets. Output of the main stored proc contains 6 result sets.

Now I have to create a report using this SP to show all the Result sets in a Single report. But When I pass the Parameter in SP in SSRS. It ends up showing just one result set with Order ID. 

Can you please guide me how can I show all the result set in a Single report? 



Multiple databases backup/restore in a transaction, if possible, into only one file


Hi dear,

I have three databases named XDW, XOLTP, XOLTPSchema. All of these are for only one application. In the application, user should do backup/restore using GUI.


1. How I should do backup/restore in one transaction? i.e. all of three actions should be success elsewhere all of them should fail.

2. Is it possible to backup them into only one file e.g. X.bak?

3. Or, What is the standard solution in this condition?

Thanks in advance.

How To: Joining multiple DataTable using “LINQ to DataSet”

Database snapshots do not work well with databases having multiple log files


It appears there is a bug in the implementation of database snapshots.

If you create a snapshot on a database with multiple log files:

Then restore from the snapshot:

then all of you log files, but one will disappear. As a result the backup command will fail with the following message:
Msg 3636, Level 16, State 2, Line 1
An error occurred while processing 'BackupMetadata' metadata for database id 12 file id 10.
Msg 3046, Level 16, State 2, Line 1
Inconsistent metadata has been encountered. The on

INSERT the Results of a Stored Procedure with Multiple SELECT Statements into Multiple Tables?

I have a stored procedure (which is overly complex and written by someone else) that I need to take the results of and put them into tables.  The stored procedure uses Dynamic SQL to build and then run 5 SELECT statements based on literally hundreds of variables.  I need to take the results of these SELECT statements and put them into tables in a different database.  Rewriting or re-creating the logic of the stored procedure is NOT AN OPTION as the logic may change over time and maintaining it in multiple places would be nightmarish.  Can anybody help me with this?  I've googled extensively, but have only been able to find examples of using one result set not multiple ones.

MSSQL 2005 JDBC driver multiple select statements in stored procedure



we have a stored procedure with multiple select in it.



Select a,b,c from table1

Select f,g,h  from table2

Select j,k,l  from table3


Sometimes select no 2 will not return any values.

when this happens  we get only 2 result sets using CallableStatement getMoreResults method. 

it seems that if a select  doesn't return something it will  not have a corresponding resultset. 

On 2000 (driver + server) version we got an empty resultset for this situation. 

Is there  any  way to have the same behaivor with 2005 ?   We tryed with 1.2 and 2.0 jdbc driver.




Updating multiple Indexes on a table - Performance issue



I have a performance issue when trying to update a table with multiple indexes. The table itself has about 280 million rows. The selection of the records is fast, about 160 ms, as it has a suitable non-clustered index. However the update itself takes over 10 minutes.

When I look at the excution plan, it shows the update to the clustered index as well 5 other non clustered index which are being affected by the statement. Unfortunately it doesn't show me how those indexes are being accessed. I suspect that the update statement is performing a full index scan against each of the non-clustered indexes in order to do the update.

So my question is this, if i add the key columns of the other non-clustered indexes as included columns on the index used to select the records for update will sqlserver use them to access the additional non-clustered indexes?

any advice greatly appreciated


CURSOR performance on multiple delete/insert and maybe poor table design


We have table documents and table documents_additonal_data, joined via key (docnum, doctype), important to know is that in documents_additonal_data we just have two more fileds valuetype and value (varchar(MAX)), and every record in doc can have variable 0-n valuetypes so there is no schema enforced in any way.

Now I've made cursor that needs to transfer history data from one older table to docs, must use cursor with order by clause because transaction_id, some bussines logic etc.  In short it takes values, have some IF logic on cursor variables, checks if there is already record for that document, some more IF logic on variables (with few simple selects) and calls insert procedure.

About insert procedure, it doesn't update values but reads current, stores old to XMLvalue (xml made with select) in storage table, deletes current, and inserts new data, reason for that is probably multiple indexes  (docs is primarly 'read from' table) and millions of records, update was slower probably because in add_data on update it usually needs to delete, insert, update in the same time. Insert procedure takes XML parameter for additional_data which is then translated and inserted via exec sp_xml_preparedocum

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 :)


How to improve the performance of multiple selection field in Infopath 2010 ?


Hi Infopath experts

I am developing an infopath form that has 4 big multiple selection fields (each field has more than 50 choices). Each time the users select a choice from the muiti-selection field, the form takes a lot of time to response. This only happened with IEs (I am using IE8 now). When I tried it with other browsers (firefox, chrome, safari), the performance increased drastically.

I have seen other people saying that IE has issues with Infopath and IE9 might solve the issues. However, our company won't upgrade to IE9 anytime soon. If anyone has workarounds for this issue, please help me with it

Thank you,



SP2010 List containing multiple steps approval


Dear all,

Im sending this message in a bottle just in case.
I administrate an eRoom community (about 2000 employees) since 6 years.

We are now looking for a new system and have deployed SharePoint as a candidate to replace eroom.

However one of our bigest  used objects are  the Approval steps databases.

Approval steps databases (=lists in SharePoint) allows a new entry to be pushed to higher level or Rejected and push back to previous level.

I wonder if there is any "out of the box" workflows able to do this job !
If not is there any specific terms to search for or any good approach to have maybee ?

The approval steps list should behave this way :
New entry > [Push to next level if approved] >  Step 2 > [Push to Step 3 if approved]/or/[Push back to Step 1 if rejected] > Step 3 > [Push to Step 4 if approved]/or/[Push back to Step 2 if rejected] and so on.


Many thanks in advance for your time!
Much appreciated.




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