Cluster Server Performance

Question

Cluster Server Performance

tendayit

Ten Centuries

Points: 1348
More actions
March 9, 2009 at 8:21 am

#126861

Hi,
I have a call centre apps running against SQL 2005 databases in a cluster environment. There is 1 active node and 1 passive node. We recently upgraded the hardware to the new configuration to improve performance. Data files are stored on drive D: and log files on drive l:. Memory is capped at 26Gb for SQL allowing the operating system(Windows 2003) to have 5 Gb available.
I generated a log of the server performance. Users complain that the server hangs for a couple of minutes before being able to work. I have attached a sample file for you to see. The sample was generated at the time users complained. There comes a time when page life expectancy drops to low levels. Is this a memory issue? Should i increase memory?
I thought that antivirus software was the cause for the sql engine working hard and had it removed. But the problem is still occurring.
If i run a trace when the problem occurs, i always see that the problem clears before i get something out of the trace. So its a momentary thing.
I have also being trying to tune the call centre databases using the database tuning advisor, adding indexes and stats where need be, but the problem still occurs.
The server is an Intel Xeon machine with 16 processors. Please advise on how to solve the problem.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

Most likely if things are running fine for most of the time then it is probably a query or a set of queries that are causing the problems. I think the first thing to do would be to identify which queries are consuming the memory during this time, most likely those that are expensive to compile.

I had pulled the original version of this off Jason Massie's Blog and have modified it a bit to help out with some other information.

My guess is that if you capture the really expensive memory queries and tune them you will be well down the road of solving the problem.

Modified version:

selectsp.spid, sp.loginame, sp.program_name, t.text, query_plan, requested_memory_kb, granted_memory_kb, used_memory_kb, mg.dop, mg.request_time, mg.grant_time, mg.query_cost, mg.timeout_sec, mg.resource_semaphore_id, mg.wait_time_ms

fromsys.sysprocesses sp INNER JOIN sys.dm_exec_query_memory_grants MG on sp.spid = MG.session_id

CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) t

CROSS APPLY sys.dm_exec_query_plan(MG.plan_handle)

whereused_memory_kb > 500

Hope this helps.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

tendayit Ten Centuries Points: 1348 More actions · Answer 2

Thanks very much. i will try this out during the course of tomorrow to see whats going on. Will keep you updated.

tendayit Ten Centuries Points: 1348 More actions · Answer 3

I tried out the script when the server was busy and identified one culprit query. Since fixing it, we have not had a problem.

Thanks very much. I will use it in future if there is an issue.

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 4

Glad to hear this helped! It definitely helps you grab those ugly ones pretty quickly. Might be worthwhile to do some tracing as well and start to grab those queries / procedures that are consuming a bunch of CPU and / or are doing a lot of reads and see if there is tuning that can be done on those. Being a good bit proactive never hurts, especially when you start to see growth in utilization of the application.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

tendayit Ten Centuries Points: 1348 More actions · Answer 5

Hi DavidB,

I have been trying to be proactive since your last word of advice and i picked up 2 more procedures that were giving a problem. These i managed to fix. I also found another one yesterday which retrieves contact numbers for an account. I have attached the procedure, the tables that use it, their indices, and the the execution plans that i got when i did a test.

In the file matter_2.sqlplan, i found a key lookup item relating to the contact number table. I thought that a covering index was required to improve query performance here, so i created the index _dta_index_ContactNumber_7_948198428__K2_K9_K10_K1_6 last night. When i did another test on the procedure this morning, i found the key lookup item still appearing.

Is my covering index not being used? How can i improve performance here. I haven't picked the procedure using the low memory sql script you gave me. Is that a good sign?

Thanks

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 6

I was able to take a quick look at this and the thing that quickly jumps out at me is that the index, _dta_index_ContactNumber_7_948198428__K2_K9_K10_K1_6, is missing idnumber as part of the index keys but has it in the include columns. That will keep that index from being properly "covering". Just move that column to the index keys and see what you get for the execution plan.

There were a couple of things missing, i.e. the function scripts and some sample data to help with looking at this so, I really can't go any farther that just looking at the index definitions to see why that Key Lookup is still remaining. If you want me to look further for other performance issues I can do that but I would need the function scripts and some sample data.

I do hope this helps. Let me know how you make out.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

tendayit Ten Centuries Points: 1348 More actions · Answer 7

Thanks for the response. Sorry i did not send the functions and sample data. I will send you the sample data and the functions. I will also make the change as you advised and see if it changes things.

Will keep you posted.

tendayit Ten Centuries Points: 1348 More actions · Answer 8

Hi,

Attached is the sample data and the functions that are used by the MatterNumberGetValid proc. I tried moving the idnumber field up the order in the [_dta_index_ContactNumber_7_948198428__K2_K9_K10_K1_6] index making it an index key but the execution plan did not change. Please have a look at it.

Thanks

Tendayi

tendayit Ten Centuries Points: 1348 More actions · Answer 9

I forgot to give you the debtordetail structures. Please find attached.

tendayit Ten Centuries Points: 1348 More actions · Answer 10

Hi David,

Hope u r well. Did you manage to have a look at the MatterNumberGetValid stored procedure?

Thanks

Tendayi

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 11

I can try to take a look again today. Can you provide the data scripts as insert statements? Thanks. I'm a wee bit busy and really can't take too much time right now, hence my lack of posts, etc. :hehe:

Also, if you could post the execution plan as it stands now that would be helpful as well. Thanks again!

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

tendayit Ten Centuries Points: 1348 More actions · Answer 12

Thanks for the response. I understand if you are busy. You can look at it when you have time. I have attached the execution plans for the query. I did not understand when you said that you would like the data scripts as insert statements. Please expand on that.

Thanks

Tendayi