LOB SQL application user / client experiences frequent slowness

I have a client who uses a Line Of Business Application that is giving them trouble. They are an agricultural producer and the software essentially runs their company. It helps them control the growing, picking, packing, shipping, sales, receivables, etc. Pretty much everything. The whole system, hardware and software was  installed new in June 2011.

What I'm hoping to get from your help is a method, a way to monitor the processes and performance around this program. I envision using the SQL studio and/or WIndows Reliability and Performance Monitor to monitor key aspects of the database performance while it's running. Hopefully, I can leave a perfmon running and maybe some live graphs that could indicate a bottleneck so Shipping user could call upstairs to the office *when they're having the problem* and the Admin User could glance at the graphs and spot a potential issue, or at least call me to take a look.

Problem and Specs

The problem primarily exists with the single Shipping computer. The entire system consists of the Server (specs below), one Admin PC ( a very new Optiplex 9090- I think- with Core i7 and SSD and plenty of RAM, Win7) and a Shipping PC - (another Optiplex, either i5 or i7, SSD and 4 GB RAM, Win7). The shipping user complains that aspects of their program slows down. It may be during printing labels or tickets, or creating a report. Generally during a time where the server would be expected to gather information and produce an output.

The problem started at the beginning of the install of this new system. At that time the shipping PC was a slightly older, maybe 2009 vostro, with decently fast specs.
-I replaced the whole PC with the fast Optiplex. It didn't solve the issue.
-I installed a good quality Gigabit managed 16 port switch at the server.
-I placed a gigabit switch at the shipping PC
-I replaced the ethernet cable from server switch to shipping switch - one single run of maybe 150'
- I get gigabit links all around.

Server Specs:
Dell Server T310. Xeon X3470 2.93 GHz, 16 GB RAM, 64 bit
MS Server 2008 Standard SP2
SQL Server 2008 R2

From SQL Management Studio:
===================================================
Microsoft SQL Server Management Studio                                    10.50.1600.1
Microsoft Analysis Services Client Tools                                    10.50.1600.1
Microsoft Data Access Components (MDAC)                                    6.0.6002.18005
Microsoft MSXML                                    3.0 4.0 5.0 6.0
Microsoft Internet Explorer                                    9.0.8112.16421
Microsoft .NET Framework                                    2.0.50727.4241
Operating System                                    6.0.6002
====================================================
RickNCNAsked:
Who is Participating?
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.

David ToddSenior DBACommented:
Hi,

Check out Brent Ozar's performance monitor pages.
http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/

And, what else is running on the server, as in what else is it doing?

I had a server that was also doing fileserver duties, and some of the designers saves could be 10 - 20GB in size, and that would cause everything to grind to a halt while saving.

HTH
  David
0
Mark WillsTopic AdvisorCommented:
So, it is the same application, but the hardware, and OS and SQL have been upgraded ?

Or the Application has also been upgraded and is verified for SQL 2008 R2 ?

If an upgraded system, what considerations were given to the SQL environment - updated stats, recompiled procedures... Is it a dedicated SQL machine ? Has there been any memory management ? What about disk optimisation (and types of storage) ?

Some of the new directions to take and what to search for are typically based on what has been done. We can see that there has been some work on the physical network, but what about the application environment ?

"slow" can be attributed to so many different things.

Normally it is a series of perfmon type tasks to help isolate the obvious bottlenecks and then dig deeper into suspect areas. With a "slow" system (especially when it appears to be periodic rather than perpetually slow) you have to be careful that you don't compound the problems by over monitoring and adding additional load.

It is a big subject area, and there are commercial tools out there (such as red-gate, idera, toad etc) which can make life easier. Some of those 3rd party tools have "free" or limited trial editions and may help to identify their worth e.g. http://www.idera.com/productssolutions/freetools/sqlcheck

But if you are reasonably handy, there is a lot of help from MS sites (where Brent Ozar has also contributed)

Such as : http://technet.microsoft.com/en-us/library/ms189081(v=sql.105).aspx

http://msdn.microsoft.com/en-us/library/ee377023.aspx

and http://technet.microsoft.com/en-us/library/ms191511(v=sql.105).aspx

and a whitepaper download : http://download.microsoft.com/download/D/B/D/DBDE7972-1EB9-470A-BA18-58849DB3EB3B/TShootPerfProbs2008.docx

which also describes quite a few queries that you could be running right now to help identify potential bottlenecks...

I strongly suggest you read through those items above to become familiar with the available tools and terminology. It will help you wade through the jargon and mystery often associated with performance issues.
0

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
RickNCNAuthor Commented:
Oh yes, that's a good point, Mark, it's the latter. While they did use this software previously on an SBS2003 server, it was a previous version. A new version was released, with a new name -really a new product-  and it prompted a whole new server. It was purpose built with all the required specs by the software maker. Their requirements were SQL 2008 R2. As for the speed and memory of the server, their recommendations were typicical. I exceeded every spec by good measure.
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

RickNCNAuthor Commented:
dtodd: It's their AD server, file server, Trend WFBS AV server and Runs BackupAssist to backup every night. I think I may have caught it misbehaving yesterday. I just opened the Task Manager and saw that BackupAssistService.exe had CPU resources pegged at 90-95%. I closed BUA (we have latest version 7.2.1) and the console was real sluggish and didn't seem to be updating / refreshing. I closed the console which didn't want to close and I stopped the BUAservice. CPU dropped to normal levels and users reported faster responses in their LOB software. I see here: http://www.backupassist.com/phpBB3/viewtopic.php?f=15&t=6207 That they've had problems with the program reading log files in a timely fashion. I checked the folders they suggested and they all really small, just a few MBs. BUA has been running fine since yesterday now and running the console at the moment everything is fine. I'll keep my eye on this. I will run Brent Ozar's suggested PerfMons and reports to try to confirm.

Thanks
0
Mark WillsTopic AdvisorCommented:
Sounds like you will need to check BUA regularly, two main perfmon metrics will be CPU queue length to be less than 1, then check memory utilisation.

Certainly BackupAssistService at 90-95% is extraordinarily high.

The hardware specs look OK - pity it is used for a few different things, but that also points the finger at being an "app server" and how to make them live happily together.

With the server performing so many different tasks, might need to consider setting max memory for SQL Server to give some of the other apps a chance to share resources. SQL on 64Bit will consume as much as it can to the detriment of other apps (sometimes including the op sys). This is something that can progressively increment and exhaust memory (depending on queries etc).

Have a read of : http://sqlserverperformance.wordpress.com/2009/10/29/suggested-max-memory-settings-for-sql-server-20052008/

And remember it is a guide, would suggest around 12gb as a starting point and grab some metrics before and after.

Part of the "black art" of measuring various stats is knowing what to then look for in terms of correction. Sometimes IO pressures reflect in Memory counts which can lead to CPU.

When converting or upgrading a database to a new version, you really should perform a reindex updating statistics and then recompile your procedures to use the updated stats in determining a plan (which is held at time of compile).

So, might also be worthwhile downloading SQL 2008R2 best practices analyser to double check : http://www.microsoft.com/en-us/download/details.aspx?id=15289
0
RickNCNAuthor Commented:
Users reported very snappy performance all yesterday. This morning both users reported some slowness again. I had been watching CPU all morning and BUAservice was NOT pegged at 90. CPU was at normal level.
0
Mark WillsTopic AdvisorCommented:
How is memory ?
0
David ToddSenior DBACommented:
Hi,

The reason I suggested Brent's metrics is that pressure in one area leads to pressure on everything else. That is, pressure on memory leads to paging, leads to high CPU ... etc.

So need to look at several markers which suggest this or that.

Are you maintaining your indexes regularly? If indexes and statistics are out of date then starting to maintain them can be almost magical in boosting performance.

Regards
  David
0
RickNCNAuthor Commented:
I read through Brent Ozar's excellent post on SQL Performance Monitoring. I followed it to a T and was able to successfully start the Data Collector. One question and then a couple comments:

Since this is running on a secondary PC, I am logged in as domain administrator. I assume I can, and should, log off and allow the regular user to log on and use her PC if needed? The perfmon collector runs as a service?

For Informational Purposes if you're following Mr Ozar's Instructions

I think I found a couple of omissions that might make a difference to people trying to use his guide. As comments seem to be disabled on his blog, I'll post it here.

He starts to walk the user through adding counter objects. He goes through five of them and gives some explanation before giving a full list. He makes a reference to the FULL list as "including the ones mentioned above:" but I found that he only included 2 out of the 5 he mentioned previously in the narrative. Here are the 5 mentioned in the earlier paragraphs and then his full list (15), Then the actual "Full List" with all 17 counters.
------------------------------------------------------------
Five counters mentioned in the "previous paragraph":
[Memory]
1- “Pages/sec” counter
2- "Available MBytes”
[Processor]
3- % Processor Time” ( highlight each individual processor,)
[Physical Disk]
4- “% Disk Time” (Highlight all of the physical disks in the instance list (again, leave off the _Total instance))
5- “Avg. Disk Queue Length”
------------------------------------------------------------
His List: (15 items)
*Memory – Available MBytes
*Paging File – % Usage
*Physical Disk – Avg. Disk sec/Read
*Physical Disk – Avg. Disk sec/Write
*Physical Disk – Disk Reads/sec
*Physical Disk – Disk Writes/sec
*Processor – % Processor Time
*SQLServer: Buffer Manager – Page life expectancy
*SQLServer: General Statistics – User Connections
*SQLServer: Memory Manager – Memory Grants Pending
*SQLServer: SQL Statistics – Batch Requests/sec
*SQLServer: SQL Statistics – Compilations/sec
*SQLServer: SQL Statistics – Recompilations/sec
*System – Processor Queue Length
--------------------------------------------------------------

The Real Full List (17 items)
1- Memory - “Pages/sec” counter
*2- Memory – Available MBytes
*Paging File – % Usage
*Physical Disk – Avg. Disk sec/Read
*Physical Disk – Avg. Disk sec/Write
*Physical Disk – Disk Reads/sec
*Physical Disk – Disk Writes/sec
4- "Physical Disk" “% Disk Time” (Highlight all of the physical disks in the instance list (again, leave off the _Total instance))
5-"Physical Disk" “Avg. Disk Queue Length”
*3- Processor – % Processor Time
*SQLServer: Buffer Manager – Page life expectancy
*SQLServer: General Statistics – User Connections
*SQLServer: Memory Manager – Memory Grants Pending
*SQLServer: SQL Statistics – Batch Requests/sec
*SQLServer: SQL Statistics – Compilations/sec
*SQLServer: SQL Statistics – Recompilations/sec
*System – Processor Queue Length

Connecting to Remote Server - don't use the "Browse" button

The other problem I had was in connecting to the remote server. I used the "Browse" button and typed in the server name and pressed the "Check Name" button. The name seemed verified and a line was added under the server name indicating that. However when I clicked OK and went back to the list of Counters and Objects, I found nothing there related to SQL or MSSQL. The server was listed as simply "SERVERNAME".

 I found I had to go back to "Select counters from Computer" and type in \\SERVERNAME. Now, it paused a couple seconds while loading the list of counters and showed me the correct list from the server.
0
RickNCNAuthor Commented:
Or what about "Switch User" instead of logging off?
0
RickNCNAuthor Commented:
Can I just run this Perfmon from the server? will it make a difference?
0
Mark WillsTopic AdvisorCommented:
Not sure I understand your question...

Yes you can run perfmon from the server - there are also some SQL DMV's that contain some of the metrics already...

Check : http://technet.microsoft.com/en-us/library/ms176083(v=sql.105).aspx for some of the DMV's and in particular : http://technet.microsoft.com/en-us/library/ms187743(v=sql.105).aspx

It wont show some of the mtrics listed above - for that you need to run perfmon.

Note however, if your machine is periodically grinding to a halt, then perfmon might contribute to resource usage / memory contention at those crucial stages. So, best to only monitor the specific metrics you need to start identifying the possible bottlenecks.
0
RickNCNAuthor Commented:
Sorry, I should have been clearer. I had asked a few questions that had gone unanswered, and I was continuing the thread:

Brent Ozar's article suggests running Perfmon not from the server. So I asked:

"Since this is running on a secondary PC, I am logged in as domain administrator. I assume I can, and should, log off and allow the regular user to log on and use her PC if needed? The perfmon collector runs as a service?"

then I wrote:

"Or what about "Switch User" instead of logging off?"

and then:

"Can I just run this Perfmon from the server? will it make a difference?"

You answered my question. I don't think the server's really ever grinding to a halt (one reason this is a little more mystifying) so running Perfmon from server should be ok.

I think I may have found the culprit in BackupAssist hogging CPU for some reason. I'm monitoring to make sure.
0
David ToddSenior DBACommented:
Hi,

Good catch on the backupAssist thing.

How did the perfmon thing work out? You did note that you can Brent or his team to give 5mins look at your finished spreadsheet ...

Regards
  David
0
Mark WillsTopic AdvisorCommented:
Understood some of those other questions, but thought you were directing them to dtodd.

Sometimes "high CPU" can reflect a computational state or kernel mode activity waiting on other resources. %Privilege Time is the more challenging compared to %User Time.

I would be inclined to go through : http://blogs.technet.com/b/davguents_blog/archive/2011/04/07/the-case-of-the-unexplained-high-cpu-condition.aspx

Your focal point should be % processor time as the best indicator of what is using CPU and compare the various processes. In the example above, it shows SQL Server as the culprit, but the same procedures equally apply to other processes.

As previously mentioned... Have you managed max memory for SQL Server to enable some memory resources for the other apps ?
0
RickNCNAuthor Commented:
After watching the system and talking to the user, I think I can proclaim the problem resolved. I think it all disappeared after I found the BackupAssist process problem. You answered my primary question about how to monitor this system with perfmon, etc. I took into account how much each of you responded by counting up the column inches of responses and adding or subtracting a little based on content. I came up with a score of 4 for dtodd and 12 for mark_wills. Total of 16, therefore dtodd gets .25 and mark_wills .75 of the points. It's the fairest way I've come up with to award points. (I wish E.E. had a better way to rate and grade answers).

Thanks for the help
0
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.