October 29, 2017 at 8:44 am
Hello Folks.
Bit of a strange one - or else I am missing something here (high probability).
I found some sample code to set up an Extended Event Session for Memory Grants and I am trying to run this on SQL Server Standard Edition 2014 (SP2 - CU7).
It is telling me that sqlserver.query_memory_grant_usage is invalid or can not be found.
I found an MS query to list all available Events and there are none starting with Query_M
I then tried using the wizard in SSMS to add the session and it doesn't list sqlserver.query_memory_grant_usage as an event.
I can find no mention of this being an Enterprise Edition only option - but maybe it is.
Any idea what I am doing wrong?
TIA
Steve O.
October 29, 2017 at 9:09 am
That event was added in SQL Server 2016
https://www.sqlskills.com/blogs/bobb/over-1000-xevents-in-sql-server-2016-ctp2-here-are-the-new-ones/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2017 at 9:12 am
Logged into 3 Instances as SA from same SSMS session
2 x 2014 Instances - returns 7604 Events from query
1 x 2016 Instance - returns 13065 Events from query - including the Query_Memory event that I want to use
I wonder if the issue may be the DBLevel of Master being backlevel somehow affecting the SP2 install on 2014 (whenever that occurred),
whereas the 2016 instance was a new build.
Or is there something else that anybody else can suggest?
Steve O.
October 29, 2017 at 9:20 am
There's nothing wrong here.
That event was added in SQL Server 2016.
It won't be present on a 2014 server. Nothing to do with the service pack, or the dbversion of master (which will be 782, since it's on a SQL 2014 instance).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2017 at 9:21 am
GilaMonster - Sunday, October 29, 2017 9:09 AMThat event was added in SQL Server 2016
https://www.sqlskills.com/blogs/bobb/over-1000-xevents-in-sql-server-2016-ctp2-here-are-the-new-ones/
Thanks for the reply Gail.
I was going by this?
Come to think of it, I have never seen the excessive memory grant warning that was also introduced in SP2?
Regards
Steve O.
October 29, 2017 at 9:24 am
Interesting. Looks like they back-ported it down to 2014 in SP2. Kinda unexpected.
What's SELECT @@Version on that server?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2017 at 9:31 am
GilaMonster - Sunday, October 29, 2017 9:24 AMInteresting. Looks like they back-ported it down to 2014 in SP2. Kinda unexpected.What's SELECT @@Version on that server?
Microsoft SQL Server 2014 (SP2-CU7) (KB4032541) - 12.0.5556.0 (X64)
Aug 17 2017 12:07:38
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
FYI - I was referring to the DB Compatibility Level of Master in the earlier post - It was set to 100 (2008) and changing it (in Test) makes no difference,
hence my idly surmising that it may have affected the install of SP2 in some way.
Thanks.
Steve O
October 29, 2017 at 10:23 pm
SteveOC - Sunday, October 29, 2017 9:31 AMGilaMonster - Sunday, October 29, 2017 9:24 AMInteresting. Looks like they back-ported it down to 2014 in SP2. Kinda unexpected.What's SELECT @@Version on that server?
Microsoft SQL Server 2014 (SP2-CU7) (KB4032541) - 12.0.5556.0 (X64)
Aug 17 2017 12:07:38
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)FYI - I was referring to the DB Compatibility Level of Master in the earlier post - It was set to 100 (2008) and changing it (in Test) makes no difference,
hence my idly surmising that it may have affected the install of SP2 in some way.Thanks.
Steve O
Looks like the kb article is in error. According to the notes for SP2 for 2014, that kb article's not included in it. Can't find it in any of the CUs I've looked through. Don't have a 2014 instance to test on
https://support.microsoft.com/en-us/help/3171021/sql-server-2014-service-pack-2-release-information
Compat mode won't affect extended events, or service packs, or database versions. It's purely about T-SQL syntax and optimiser behaviour.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2017 at 7:57 am
Thanks Gail.
Couldn't think of any reason why it didn't work, so was just wondering if the install choked at some point.
Regards
Steve O.
October 30, 2017 at 7:07 pm
SteveOC - Monday, October 30, 2017 7:57 AMCouldn't think of any reason why it didn't work, so was just wondering if the install choked at some point.
Regards
Steve O.
Nah. Service packs work or don't.
You're not going to get a service pack installing some of the fixes and leaving a working instance. Broken instance that needs a reinstall/repair maybe.
It's a weird one. I found blog posts indicating that the fix in question was in SP2, but the kb article for SP2 doesn't list it, so not sure what happened there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply