May 13, 2009 at 3:07 pm
If your business applications connect providing all possible connection info (application name, workstation id,...) ....
those non-business application connections can easily be rejected using a login trigger and only allow certain predefined applications or hostnames.
In many cases, also in my shop , business responsables will not let you take such rigid measures because they fear support might be endangered.
Which is rubbish btw.
It may be a way out of stressed situations. (as well for you sqlserver, as for your server, as for your team, as for yourself)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 29, 2009 at 2:42 pm
a resource semaphore wait type is typically waiting on a memory grant, check pending/outstanding memory grants in perfmon. That's normally because a query plan has compiled in a "non-optimal" fashion.
You can identify which queries are causing this to occur by running the query below.
You may need to be more selective with youre where clause or add an index, identify the query, go through the tuning process and sort it out.
Production server you say, maxdop to 1 mate, every time. Thats many years of experience talking. Maxdop is a waste of time on anything but pure reporting systems in my experience. Ive seen it grind whole companies to a halt, the default setting pre-2008 is really unsafe, unpredictable and shit. Just turn parallelism off for a more predixctable experience IMO.
--Replacement for master.dbo.sysprocesses
SELECT r.session_id -- new column for SPID
,r.database_id
,r.user_id
,r.status
,st.text
,r.wait_type
,r.wait_time
,r.last_wait_type
,r.command
,es.host_name
,es.program_name
,es.nt_domain
,es.nt_user_name
,es.login_name
,mg.dop --Degree of parallelism
,mg.request_time --Date and time when this query requested the memory grant.
,mg.grant_time --NULL means memory has not been granted
,mg.requested_memory_kb --Total requested amount of memory in kilobytes
,mg.granted_memory_kb --Total amount of memory actually granted in kilobytes. NULL if not granted
,mg.required_memory_kb --Minimum memory required to run this query in kilobytes.
,mg.query_cost --Estimated query cost.
,mg.timeout_sec --Time-out in seconds before this query gives up the memory grant request.
,mg.resource_semaphore_id --Nonunique ID of the resource semaphore on which this query is waiting.
,mg.wait_time_ms --Wait time in milliseconds. NULL if the memory is already granted.
,CASE mg.is_next_candidate --Is this process the next candidate for a memory grant
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Memory has been granted'
END AS 'Next Candidate for Memory Grant'
,rs.target_memory_kb --Grant usage target in kilobytes.
,rs.max_target_memory_kb --Maximum potential target in kilobytes. NULL for the small-query resource semaphore.
,rs.total_memory_kb --Memory held by the resource semaphore in kilobytes.
,rs.available_memory_kb --Memory available for a new grant in kilobytes.
,rs.granted_memory_kb --Total granted memory in kilobytes.
,rs.used_memory_kb --Physically used part of granted memory in kilobytes.
,rs.grantee_count --Number of active queries that have their grants satisfied.
,rs.waiter_count --Number of queries waiting for grants to be satisfied.
,rs.timeout_error_count --Total number of time-out errors since server startup. NULL for the small-query resource semaphore.
,rs.forced_grant_count --Total number of forced minimum-memory grants since server startup. NULL for the small-query resource semaphore.
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions es
ON r.session_id = es.session_id
INNER JOIN sys.dm_exec_query_memory_grants mg
ON r.session_id = mg.session_id
INNER JOIN sys.dm_exec_query_resource_semaphores rs
ON mg.resource_semaphore_id = rs.resource_semaphore_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)st
--Is anything timing out
SELECT * FROM sys.dm_exec_query_optimizer_info
WHERE counter = 'timeout'
anyway thats from here, which does a pretty spot on job of explaining resource semaphores.
October 30, 2009 at 12:36 pm
Paul Hayes-294329 (10/29/2009)
a resource semaphore wait type is typically waiting on a memory grant, check pending/outstanding memory grants in perfmon. That's normally because a query plan has compiled in a "non-optimal" fashion.You can identify which queries are causing this to occur by running the query below.
You may need to be more selective with youre where clause or add an index, identify the query, go through the tuning process and sort it out.
Production server you say, maxdop to 1 mate, every time. Thats many years of experience talking. Maxdop is a waste of time on anything but pure reporting systems in my experience. ...Just turn parallelism off for a more predixctable experience IMO.
--Replacement for master.dbo.sysprocesses
...
Thanks very much for the query identifying queries waiting for memory grants, I'll add to my tool box.
BTW, I totally agree on the MAXDOP comment, I've been having the same experience.
CXPACKET waits (parallelism) are just a mask of something more sinister going on with query performance, and not just on Halloween night... :w00t:
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 30, 2009 at 2:45 pm
CXPACKET waits (parallelism) are just a mask of something more sinister going on with query performance
Hmm - in my experience CXPACKET waits are almost always the result of a suboptimal IO system. While I do consider this a client SIN, I don't think it is really SINISTER! :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 30, 2009 at 3:15 pm
Intel's new i7 Xeons support Hyperthreading and it actually works this time
October 30, 2009 at 3:22 pm
bcronce (10/30/2009)
Intel's new i7 Xeons support Hyperthreading and it actually works this time
It always worked (exclusive of bugs). But it did (and in my completely uninformed opinion will continue to) cause (sometimes significantly) suboptimal performance on various and sundry SQL Server workloads.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 4, 2009 at 6:39 am
I have somewhat similar issue and hence querying in this thread.
To improve performance plus our Db svr was coming to the end of its warranty we decided to go in for a new Db server with higher specs than the old one.
Old svr spec: W2003Sp2, 8GBRAM, SQLSvr2005 Sp2
New svr spec: W2003Sp2, 32GBRAM, SQLSvr2005Sp2
One of our reporting server fires 12 concurrent queries to the database and new severe fails to return indefinitely. This however works fine on the old sever.
Found that the 9th query goes into a wait mode with wait_type as RESOURCE_SEMAPHORE and grant_time as NULL indicating memory has not been granted.
O/p attached.
November 4, 2009 at 7:34 am
TheSQLGuru (10/30/2009)
CXPACKET waits (parallelism) are just a mask of something more sinister going on with query performance
Hmm - in my experience CXPACKET waits are almost always the result of a suboptimal IO system. While I do consider this a client SIN, I don't think it is really SINISTER! :w00t:
They are a symptom of parallelism being turned on, absolutely nothing else LOL.
Just set parallelism to 1, the default settings in 2005 are shit and seriously negatively affect production OLTP Loads. Set parallelism to 1 and they go away, you may see other problems mind.
November 4, 2009 at 8:11 am
Changed Degree of parallelism to 1 from 4 but same result.
November 4, 2009 at 8:31 am
pauljacob (11/4/2009)
Changed Degree of parallelism to 1 from 4 but same result.
Sorry, what result?
If its a resource_semaphore wait, have you tried recompiling the sproc?
Is your where clause specific enough?
Do you have appropriate indexes on the relevant tables.
Ive seen this happen for the two reasons above.
How many tables are you joining
could you split those joins using temporary tables / @table variables as intermediary results stores.
Thats what I did to resolve.
November 4, 2009 at 8:37 am
Yes still hangs on Query num 9. Query is bascially fired from our reporting server using Cognos applications. Yes alternative solutions are to spilt the queries but it works fine on the old db server hence keen to see what is causing this issue on the new server which supposedly is twice or more powerfull. Hence I doubt query is causing any issues.
November 4, 2009 at 3:27 pm
pauljacob (11/4/2009)
Yes still hangs on Query num 9. Query is bascially fired from our reporting server using Cognos applications. Yes alternative solutions are to spilt the queries but it works fine on the old db server hence keen to see what is causing this issue on the new server which supposedly is twice or more powerfull. Hence I doubt query is causing any issues.
Sounds like time (or past time really) for a call to Microsoft...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 4, 2009 at 4:27 pm
Its difficult to say without knowing more about the query and the schema. I would start by analysing the results from the post I made earlier, then maybe comparing the query plans between the two. Is it a custom report or third party provided code, Is it using the same indexes (you could try variants of different index hints and measure/compare the results)? Are the index statistics up to date (try running sp_updatestats, then recompile)? Could the query benefit from a covering index?
Ive only seen the semaphore timeout happen when the query cost is massive, and that was when a query was built for a small data set then the data set grew so large, the query was recompiled in an inefficient manner. When compiling a query plan the query optimizer takes into account loads of factors, number of joins in the query, number of rows returned, number of indexes, data value distribution within the indexes, number of physical reads, number of logical reads. Each of the different methods that can be used to satisfy a query are evaluated the query processor picks what it thinks is the query plan with the lowest query cost, the more complex the query, the more possible choices available, the higher the risk that the wrong choice could be made. In most cases SQL Server does a great job but sometimes it needs to be encouraged down a certain path. Certainly changing the hardware infrastructure can change the profile of the query but it is not always for the better though lol
November 26, 2009 at 9:10 pm
Hi
You can use @nalytics Performance Free Data Collector for Microsoft SQL Server & Windows
Server, this tool can help you to solve your performance problems, I have tested it works
excellent and it is very easy to configure and implement it.
Regards
@Analytics Peformance - Microsoft SQL Server & Windwos Server Data Collector
February 23, 2010 at 9:11 am
I'm wondering if this issue has been resolved, and if so, what was the solution.
Also, this KB article looks like it might be promising.......if you're not already at this patch level:
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply