SQL server performance

We are banking company and each branch has separate database in one SQL server. Database instance is on one hardware which has SAN storage which is in datacentre . Each branch users enters data through IIS based application which is processed through transaction processing system using MSDTC and MSMQ. We are facing performance issues as after user saves data, it gets saved successfully but takes lot of time in background processing in Msg Q processing and database processing. This issue does not occurs when less number of branches operating at the same time. Please help me how and where I can find the exact issue? in hardware performance or code performance?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Your issue can ne:
Network latency/bandwidth to the data center
But since each branch has their own DB on a single instance of SQL, your issue is likely the  lack of memory resources in the system forcing paging/swapping
Read/write from/to disk is likely what slows down the performance.

What OS/SQL version and make/model of hardware?
See what is the maximum memory each supports oS/make model Server.

You could use perfmon to confirm using memory, disk queue read writes general SQL performance baselining.

Oh, generally, SQL which is a data accumulator, more ram is often the correct quick fix.

Not her more complicated option if available is to use other schemes within the database to minimize the data being access/needed in normal day to day operation.

I.e. The more you can minimize the operational data, the less memory it will need to service them.
Scott GorcesterCTOCommented:
How many databases are you running on a single SQL server? What resources does this server have (CPU and Memory). What type of SAN, how is it connected, what type of drives and how many drives? What is the speed of the connections between the users and the IIS servers and what is the speed between the IIS servers and the database servers.

I would check these basic things first.

ADAuthor Commented:
Hi Arnold,

First of all, thanks for offering the help. I'll try to give as much information as I can.

Bandwidth could not be an issue cause as I said users can save data without any issue (Screen response is good) but background data processing takes long time within MSQ and database processing.

Datacentre provider manages hardware infrastructure.

OS: Windows Server 2008 R2 datacentre 64 bit.
RAM 1.00 TB

Using perfmon we could see that disk I/O was high and few months back we have added some more SAN disks and shifted some data files there and performance was improved to some extent but now due to cost factors adding any more resources is not a good option and we are on pressure in optimizing the performance within available resources.

What are the schemes that you are talking about?
Is there any straight forward way to minimize operational data?

We saw that when more number of branches are connected and operating at the same time performance degrades. Is this something to do with configuration of simultaneous connections  in SQL server?

Below are some actions will be performed in near future. Could you review this?

Actions -

a. Removal of unused indexes
b. Removal of bad indexes
c. Analysis of Deadlocks traces

a. Review any missing indexes or addition of  new indexes

a. Review Duplicate indexes
b. Code and query optimization (Detailed assessment has been carried out to identify the problematic components & queries)
c. Action on Deadlocks traces
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

ADAuthor Commented:
Hi moosesupport,

We have 25 databases on 1 SQL server which is on gigantic hardware. This hardware infrastructure is provided and supported by datacentre provider so not sure how they configure SAN but it is as per industry standard.
All 1Tb is consumed.  The system is not swapping. The taskmgr is just one.  How big are the databases?
ADAuthor Commented:
Hi Arnold,

Databases sizes varies depends on number of the customers - a smallest branch is around 35gb and largest or oldest branch is around 60gb. Perhaps performance issues can occur on any of the branch database irrespective of size of the databases.
The 1TB RAM is not large enough to maintain all Dbs in memory.
While preserving access to the data, having non current data offloaded to a separate instance/DB I.e would allow for a more manageable DB sizes that are regularly accessed. DB partitioning if the option is available might help
ADAuthor Commented:
DB partitioning was the option proposed but not implemented since the kind of application which we are using may not support it. We have implemented it for other applications but not this one. Proposal to archiving the non-current data is already in process but may take some time depending upon the resource availability and etc.
Sorry its very basic but - could you share the query which I can run for a particular database to know which SP or Query has taken more execution time, more hits and timeouts?
Scott GorcesterCTOCommented:
I would surmise that you are simply putting too much load on this system. You say the datacenter provides the hardware and its built to industry standards. Its often not sufficient to simply build to industry standards as every application is different. The right answer would be to say "it was built to the specific performance requirements of our load." In extrememly high load requirements it requires heavy analisys and testing to design a solution that can provide you the performance you require. I suspect the scenario is a little like buying a truck that is designed to haul 10k LBs and then dumping 30k lbs of rock on it. My feeling is your system is not designed to handle the load you have placed on it. The work that I have done to tune SQL performance often involves an indepth review of storage IOPS requirements, tuning the code and optimizing the SQL installation. I would recommend that you hire a DB expert and server expert that can perform the proper analysis and get the issue figured out.

Adding another DB server and shifting/redistributing the load some active large DBs with some smaller DBS to the new DB server likely of similar specs is needed this will extend//improve if the current growth of data for another 2/3 of the  period it took to get you initial setup to this point.

An alternate solution each branch has its own resource that is transactionally replicated/merged to the main.......
Or just have the main with warehouse type data....
Gerald ConnollyCommented:
I dont think i would be comfortable running windows in a system that has 96% memory utilisation.

Windows isnt usually happy above 80%, it usually likes a bit of room to manoeuvre in!
ADAuthor Commented:
Could you please share a dmv query that can show what is causing high memory and IO contention so as to understand if there is any scope for code optimization?
ADAuthor Commented:
Hi Gerald,

Are you saying memory allocation to SQL and OS should not be done over 80 % of total memory?
In the current setting, what is the and where is the paging file.
When starting the paging file being low is fine as there is a large amount of available RAM (Physical Memory). Now that the DBS have grown to a combined size exceeding the physical ram, the increase of paging file should help.

Properties of computer, advanced system settings, performance settings, advanced look to make sure programs are prioritized for processor and system cache.  Look at the windows recommended size for the paging file.

See if you have enough space on local drives to increase that.  If possible add drives to handle just this functionality.
If my guesstimate is close to right, the windows recommended size might be in the 400GB range.
You do not need to have a single file, but can be spread among different drives.
ADAuthor Commented:
Attached are the current settings. Currently there is no scope to increase virtual memory. Is there any way to gather evidences that memory is over utilized or insufficient?
Scott GorcesterCTOCommented:
Absolutely agree you need a large page file. Windows server will page memory no matter how much physical memory you have.

Note the windows recommended page file is approximately 1.5TB

Does your system have space for drives into which you can add a pair of 1TB and use them solely for paging?
See ref example, http://blogs.msdn.com/b/indrajit/archive/2013/12/12/sql-server-database-engine-performance-tuning-basics.aspx

In this stage disk queue and memory hits may shed some ....
Here is a more descriptive/encompassing perfmon counters to consider/evaluate though at this stage
I think the performance can be improved through the addition of large sata if your system's raid controller can handle mixed disks.

Capture a set to have a baseline to compare after the added drive function as the paging files.

The addition and impact Should stabilize ......
Gerald ConnollyCommented:
Its obvious from your description that you have a resource issue.

If the system works OK but the performance suffers once you start applying load is the give-away.

You need to sort out which resource (probably resources) you are running out of!

Is it horsepower (CPU)? Doesnt sound like it now, but after other tuning it might be.
Memory (RAM)? This sounds probable.
Disk? There are at least three potential issues here 1) Paging - With a 6GB page file but Windows recomending 1.5TB, 2) Throughput, are there enough available MB/s for the load. 3) Bandwidth, are there enough IOPS available for the load

and then there is the DB tuning!!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ADAuthor Commented:
We have set the performance counters and will soon come up with detail improvement plan. These parameters and comments provided so far will definitely help us.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.