Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ddavis539
Starting Member
2 Posts |
 Posted - 2011-08-05 : 18:10:17
|
| I have a website backed by a SQL Server 2005 database which we've been load and performance testing over the past couple of weeks. During our testing we found a bottleneck during the login process which involved selecting the fields from a user table. Under load (~300 virtual users), we're finding this query is taking 30 seconds or more.The query is straightforward, it just selects all of the fields of the table where the username matches a passed in username. This table has an integer sequence as a primary key (I confirmed that there is the auto-generated clustered index on this field). When I first saw this, I noticed that there wasn't an index on the username field used in the query, so I created a nonclustered index on that field (varchar(50)) and we reran the performance test.After adding the index and re-running, I'm not seeing any improvement in the performance. I'm still seeing the query take up to 30 seconds. We have logging enabled for the test, so I can see right before the query is executed and right after. The pattern I'm seeing several threads stack up and wait on the "before query" part of the code, before they begin exiting one by one (similar to the thread pattern seen around a synchronized block in the java code).The application does write back to this record to update the last login time, so I don't really have the option of using the "with (NOLOCK)" select clause. Is this caused by the escalating locking "feature" of SQL server? Is there something else I could do to optimize this loading? The table in the schema I'm testing only has 25,385 records in it, which isn't all that much. Our production system has 10x that and continues to grow. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ddavis539
Starting Member
2 Posts |
Posted - 2011-08-08 : 12:55:42
|
| The query returns a single row, using a passed in username parameter. We're using hibernate, so the derived query looks a little strange, but it just selects all of the columns in the table:select maxuser0_.Id as Id50_, maxuser0_.Individual_Id as Individual2_50_, maxuser0_.User_Name as User3_50_, maxuser0_.Password_Expire_Dt as Password4_50_, maxuser0_.Is_Temp_Password as Is5_50_, maxuser0_.Last_Login_Dt as Last6_50_, maxuser0_.Security_Question1_Id as Security7_50_, maxuser0_.Security_Answer1 as Security8_50_, maxuser0_.Security_Question2_Id as Security9_50_, maxuser0_.Security_Answer2 as Security10_50_, maxuser0_.Hashed_Password as Hashed11_50_, maxuser0_.Hashed_Password1 as Hashed12_50_, maxuser0_.Hashed_Password2 as Hashed13_50_, maxuser0_.Hashed_Password3 as Hashed14_50_, maxuser0_.Hashed_Password4 as Hashed15_50_, maxuser0_.Login_Attempts as Login16_50_ from Max_User maxuser0_ where maxuser0_.User_Name='testlearner827@sliqa.com'I've uploaded the execution plan to this url:https://docs.google.com/leaf?id=0B7yG2uTrlqvkMTY5NDQwYWMtNjZjYi00YjBkLTkzZWMtOWU3MGJjYzU1MTFi&hl=en_US |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-08-09 : 02:49:41
|
| Try creating a nonclustered index on username which covers the column Id.PBUH |
|
|
|
|
|
|
|
|
performance under load question