1

I am fighting with huge performance problems on my website. I tried nearly everything I can image, i can google or I know to solve this, but no help.

My site loads in around 700-800ms to 2.000-2.500ms. I can isolate the problem exactly on database querys - on a single site without a single query I have 110ms, for every query, as simple as it might be, i must add around 60ms to 150ms, so a simple site with: 1) Get User-Object, 2) Get all User for an online List, 3) Get a Friendlist for this user, means I allready at 300+ms. A site with more data to load, lets say 5 simple querys, needs around 800+ to load. The curios thing is, that my SQL Performance test says every query do not need 1ms itself!

What I try until now:

  • Try the same database on another database-server, no performanse-increase
  • Try another server for IIS, so host the page on another server, no performance-increase
  • Try database on different server versions (2005, 2008 and 2012), no increase
  • Be sure the load is not caused by much users (same load times on a fresh SQL Server with me as only user)

Here is a sample in my site.master, the default.aspx display nothing, so its empty, only datrabase query, this site will load in 300ms:

Guid userID = Guid.Parse(Request.Cookies["UserID"].Value);
Dal.User user = DAL.UserDAL.GetUser(userID);
List<DAL.User> onlineList = DAL.UserDAL.GetAllOnlineUser();
List<DAL.UserFriend> friendList = DAL.UserDAL.GetAllFriends(userID);

In my DAL:

 public static User GetUser(Guid userID)
    {
        using (RPGDataContext dc = new RPGDataContext())
        {

                User u = (from a in dc.Users where a.UserID == userID select a).SingleOrDefault();
                if (u != null && u.UserID != Guid.Empty)
                {
                    return u;
                }
                else
                {
                    return null;
                }

        }
    }

 public static List<User> GetAllOnlineUser()
    {
        using (RPGDataContext dc = new RPGDataContext())
        {
            return (from a in dc.Users where a.RefreshPing > DateTime.Now.AddMinutes(-15) orderby a.Username select a).ToList();
        }
    }
  public static List<UserFriend> GetAllFriends(Guid userID)
    {
        using (RPGDataContext dc = new RPGDataContext())
        {
            DataLoadOptions options = new DataLoadOptions();
            options.LoadWith<UserFriend>(a => a.User);
            options.LoadWith<UserFriend>(a => a.User1);
            dc.LoadOptions = options;

            return (from a in dc.UserFriends where a.UserIDActive == userID select a).ToList();
        }
    }

So basicly its very easy querys, very easy code, no place for much mistakes!

I alsy runned the DataWizard SQL Profiler on my live-website while 12 users are online, call a normal site with a few (10-12) querys which loads in around 1,5 sec, thats the result: http://abload.de/image.php?img=asdzxkdd.jpg

I don't know whats to do... the database is set up properly, PKs are Guids with PK on it, FK are all set properly, here a screen: http://abload.de/image.php?img=asdasdzqjin.jpg

Ideas I have, but nothing I can found about in the internet: - Are Guid as PK? May this be a reason the database is so slow? - maybe its a problem with the connection itself, is it okay to open a new DataContext every Query?

No more ideas... Hope someone will help here... I am a little bit helpless!

Update 1

After a few suggestions on Indexing I set the Index on UserID and RPGID, the with 95% most Coloumns which are used in Where-Clauses but not a single ms increase...

PassionateDeveloper
  • 14,558
  • 34
  • 107
  • 176
  • Did the DataWizard SQL Profiler add any indexes? – tgolisch Sep 24 '13 at 14:41
  • Might be an issue with connection pooling and connection initialization. Have a look at http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx An interesting item is MinPoolSize. Also keep in mind that connections are reused based on the same connection string. – jbl Sep 24 '13 at 14:57
  • The profiler simply records database traffic. After you did this, you were supposed to run the "Database Tuning Advisor" (in SSMS, menu: Tools, Database Tuning Advisor"). It will generate a list of recommended indexes, etc and a SQL script to apply those improvements. If your DB isn't indexed-well, then your performance will be pretty bad. – tgolisch Sep 24 '13 at 14:58
  • @tgolisch: Thank you, very nice idea, i dont set any index until now, but this DTB I dont have, I work with the SQLEXPRESS Versions here, also the SSMS Express - can you help me here please? – PassionateDeveloper Sep 24 '13 at 15:02
  • Is there network latency between the web server and the database server? If you have access to log into the web server, you could run a continous ping from the command prompt there against the DB server, and see what the responses are like. – Josh Darnell Sep 24 '13 at 15:11
  • its the same server on live production. – PassionateDeveloper Sep 24 '13 at 15:24
  • 2
    Maybe it would be worthwhile to create a throw-away console application that just calls all those static methods with the same parameters and uses the `StopWatch` class to time the calls. This would make it easier to test potential improvements like if you shared a `DBContext` between the queries. – Merlyn Morgan-Graham Sep 24 '13 at 15:24
  • Have you done a ping/tracert from the IIS box to the SQL server. Sounds like they're going round the world to connect to each other. – mattmanser Sep 24 '13 at 16:08
  • its localhost, both on the same server – PassionateDeveloper Sep 24 '13 at 16:10
  • So only comment our the lines from Using to using-end? – PassionateDeveloper Sep 24 '13 at 16:43
  • How do the CPUs and memory on your server look (using TaskMan)? – tgolisch Sep 24 '13 at 17:20

2 Answers2

2

Since you have not added any indexes yet, that is most likely to be your problem. Without good indexes, as your database grows, the response time will become much slower.

It sounds like you have already run the performance profiler, which gathers query information and statistics. Next step is to run those results through the database tuning wizard. The easiest way to get to it is through SSMS (menu: Tools, Database Engine Tuning Advisor). Normally, I would not recommend blindly accepting the recommendations, but it is a great way to start.

You could read more about using the Tuning Wizard in this MSDN article: http://msdn.microsoft.com/en-us/library/ms186354(v=sql.100).aspx/html Or if you are interested in alternative ways to tune a database or monitor your indexes, here is another SO post that has some really great advice that is relevant to your question: SQL Profiler and Tuning Advisor

Community
  • 1
  • 1
tgolisch
  • 6,549
  • 3
  • 24
  • 42
  • I think this should be a comment on the question. It doesn't really provide an answer. It's just a pointer to some tools that might or might not help solve the OP's problem. – Josh Darnell Sep 24 '13 at 15:17
  • He said he hasn't set any indexes yet. I'd bet my paycheck that his database would perform better after running the database tuning wizard and applying the results. Very few things will slow down DB queries worse than table scans (missing indexes). – tgolisch Sep 24 '13 at 15:41
  • Okay, tgolisch, can you tell me how to set, pls see my comment above! – PassionateDeveloper Sep 24 '13 at 15:43
  • 1
    I agree that it is possible (maybe even likely) that the problem is missing indexes / etc (though I wouldn't bet my *whole* paycheck). But this still doesn't belong in the answer section. See: [Are answers that just contain links elsewhere really “good answers”?](http://meta.stackexchange.com/questions/8231/are-answers-that-just-contain-links-elsewhere-really-good-answers) and [What is an acceptable answer?](http://meta.stackexchange.com/questions/118582/what-is-an-acceptable-answer) – Josh Darnell Sep 24 '13 at 15:54
  • The DAT seems to only be in the standart not in the express editions, so no help for me, I try on your other link. – PassionateDeveloper Sep 24 '13 at 16:14
  • 1
    Thank you @jadarnel27. You have a good point. I have added more of an answer with links to back-up my advice. – tgolisch Sep 24 '13 at 16:15
  • @tgolisch as the leck of my SQL Admin experience, can you provide a tool for finding out whats good to set indexes alse then this? – PassionateDeveloper Sep 24 '13 at 16:20
  • Before the tuning wizard existed, I used to search the results of the profiler for the queries that took the most time. Then I would look at the WHERE clauses and the JOIN (ON) criteria to determine where I could put indexes. It was tedious, but it worked. – tgolisch Sep 24 '13 at 16:20
  • Okay seems this, but i know nearly every possible answer to that: UserID and RPGID - that is the only 2 Ids that are used in 90% of all querys - so set an Index on this and finished? – PassionateDeveloper Sep 24 '13 at 16:28
  • Have set the indexex - not a single ms increase – PassionateDeveloper Sep 24 '13 at 16:37
  • As far as I see the Indexex are usefull on a high rowcount, my databsae doesnt contain any table with over 100 rows atm – PassionateDeveloper Sep 24 '13 at 16:37
  • Based on your code/queries (above), I would want 4 (separate) indexes: { Users.UserID, Users.RefreshPing, Users.Username, and UserFriends.UserIDActive }. – tgolisch Sep 24 '13 at 17:25
  • At this point, I think indexes will make little or no difference (considering there are <100 rows in all these tables). – Josh Darnell Sep 24 '13 at 17:27
2

Standard process for troubleshooting performance problems on a data driven app go like this:

  1. Tune DB indexes (eliminated)
  2. Check resource utilization: CPU, RAM. If your CPU is maxed-out, then consider adding/upgrading CPU or optimize code or split your tiers. If your RAM is maxed-out, then consider adding RAM or split your tiers.
  3. Check HDD usage: if your queue length goes above 1 very often (more than once per 10 seconds), upgrade disk bandwidth or scale-out your disk (RAID, multiple MDF/LDFs, DB partitioning).
  4. Check network bandwidth (eliminated)
  5. Optimize code: a) consolidate DB calls b) stronger use of caching (or sessions) c) tune individual queries. Consider buying a product like RedGate Ants or equiv.

And then things get more specific to your architecture, code and platform.

tgolisch
  • 6,549
  • 3
  • 24
  • 42