• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 691
  • Last Modified:

SQL and IIS7 performance analysis

Hi Experts!
We are using SQL 2008 64 bit.
We did several updates to our database in recent months, and reduced a size of the database from 30GB down to 3GB by deleting some archived data.
Performance of our IIS seems to have actually gotten worse. Are there any tools out there to analyze stored procedures, tables, queries to determine where a bottleneck may exist? Or do you have other ideas?

P.S. It is not the server hardware. We have a 12 core processor server with 48GB of memory and 15000 rpm hard drives. The Operating system is windows 2008 r2 64bit.
1 Solution
It could be anything from bad design, data fragmentation causing poor IO (esp. since you shrank the db it seems like, and that causes massive fragmentation), bad architecture, or something on the .net side like a bad network.

Do this:
select *
from sys.dm_os_wait_stats
order by 3 desc

Paste the first 50 rows in your reply.  Do yo have a baseline?  Perfmon enabled?  If not, enable Perfmon and capture:
Avg Sec/Read
Avg Sec/Write
Total Memory
Free Memory
CPU% Usage

for about a day.

Share the results with us and we can tell you what to do.  Good luck.
Do you have IIS and SQL Server on the same box?  I have been told it is recommended you split them.  Also,  when I ran into a performance issue for a website running on SQL Server, I had create an APPS POOL in IIS for the site, it made things much quicker.  
As @MrAli posted there could be many reasons for the slowness.  Posting some statistics as recommend might help pinpoint the issue.  I have uses SQL Profiler and DTA to help identify bad queries in the past.   Do you run SQL Maintenance jobs (Update Stats, Indexing) ?
vituxaAuthor Commented:
Here are my first 50 rows in the file attached:
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Anthony PerkinsCommented:
Any chance you can answer the following questions asked here http:#a38331656 ?  Here they go again:
Do you have IIS and SQL Server on the same box?
Do you run SQL Maintenance jobs (Update Stats, Indexing) ?
vituxaAuthor Commented:
No SQL Maintenance jobs (Update Stats, Indexing)
Yes, both SQL and IIS are on the same box
Anthony PerkinsCommented:
Yes, both SQL and IIS are on the same box
That is a problem.  You have a choice:
1. (Recommended)  Install SQL Server standalone, on a separate box from IIS.
2. If you cannot do 1 than you will have to cripple SQL Server by limiting the amount of memory it uses so that it does not compete with IIS.

No SQL Maintenance jobs (Update Stats, Indexing)
Are you saying you have not updated stats or reindexed your tables?

Performance of our IIS seems to have actually gotten worse.
Why did you expect performance would improve by deleting data?  If the database is well-built there should not be any difference in performance if the size is 3GB, 30GB or 300GB.  The reason it may actually have got worse is perhaps because you did not reindex or even udpate stats after deleting the data.  Do you need help with that?
vituxaAuthor Commented:
Do you need help with that?
yes I do!!! Desperately!!!
Anthony PerkinsCommented:
The easiest thing you can do is update stats.  Just run the following:
USE YourDatabase
exec sp_updatestats

If you select Results to Text you will see the progress. Otherwise click on the Messages tab.  It should not take longer than 5-10 minutes.  This will not affect anyone.

The next thing you should do is go to this URL http://ola.hallengren.com/.  What you are after is the IndexOptimize SQL Script.  Download it (there are also some supporting scripts that are required) and install it.  When you have a window run it.  This script will intelligently re-index your tables based on the existing level of fragmentation.  I suspect it will run for about 30 minutes.  Just be aware that reindexing/reorganizing does make use of the Transaction Log, so if you are in Full-Recovery Model you may need to back it up frequently or it will grow.
vituxaAuthor Commented:
thank you

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now