September 3, 2009 at 11:22 am
Jeff:
Please make sure you don't ever accidentally/intentionally go to http://www.sqlservercentral.com/Forums/Topic782099-338-2.aspx
(Sorry, Paul, I couldn't resist!)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 3, 2009 at 2:55 pm
On behalf of myself and any others that didn't see it without your hint GSquared - thanks a lot - great laughs
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 3, 2009 at 3:29 pm
hmmm - guess he didn't want to post his code
http://www.sqlservercentral.com/Forums/Topic782432-146-1.aspx
does that mean he has learned how to research the issue and troubleshoot?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 3, 2009 at 3:45 pm
CirquedeSQLeil (9/3/2009)
hmmm - guess he didn't want to post his codehttp://www.sqlservercentral.com/Forums/Topic782432-146-1.aspx
does that mean he has learned how to research the issue and troubleshoot?
Well, he posted it then deleted it.
I somehow doubt he's learnt research and troubleshooting, and I have no idea how he could have fixed the problem. As far as I know, UpdateUsage fixed inaccurate page space records in the data file, not in the log file.
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
September 3, 2009 at 4:08 pm
CirquedeSQLeil (9/3/2009)
does that mean he has learned how to research the issue and troubleshoot?
Based on this I have to say 'No'
http://www.sqlservercentral.com/Forums/Topic782601-5-1.aspx
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
September 3, 2009 at 4:17 pm
GilaMonster (9/3/2009)
CirquedeSQLeil (9/3/2009)
does that mean he has learned how to research the issue and troubleshoot?Based on this I have to say 'No'
Could it be a language issue? Is BOL translated to languages other than English? That's the kindest explanation I can come up with.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 3, 2009 at 4:37 pm
Grant Fritchey (9/3/2009)Is BOL translated to languages other than English? That's the kindest explanation I can come up with.
It is, but not having a clue what his native language is I can't say if BoL is translated for him.
http://msdn.microsoft.com/zh-cn/library/ms174269.aspx
His english isn't bad, it's just abrupt.
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
September 3, 2009 at 4:54 pm
GilaMonster (9/3/2009)
Grant Fritchey (9/3/2009)Is BOL translated to languages other than English? That's the kindest explanation I can come up with.
It is, but not having a clue what his native language is I can't say if BoL is translated for him.
http://msdn.microsoft.com/zh-cn/library/ms174269.aspx
His english isn't bad, it's just abrupt.
Too big of a clue on that one. 😉
Someday it (t-shoot and research skills) will come around...I hope.
--How much you wanna make a bet I can throw a football over them
--mountains?... Yeah... Coach woulda put me in fourth quarter, we would've been
--state champions. No doubt. No doubt in my mind.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 3, 2009 at 5:55 pm
CirquedeSQLeil (9/3/2009)
GilaMonster (9/3/2009)
Grant Fritchey (9/3/2009)Is BOL translated to languages other than English? That's the kindest explanation I can come up with.
It is, but not having a clue what his native language is I can't say if BoL is translated for him.
http://msdn.microsoft.com/zh-cn/library/ms174269.aspx
His english isn't bad, it's just abrupt.
Too big of a clue on that one. 😉
Someday it (t-shoot and research skills) will come around...I hope.
--How much you wanna make a bet I can throw a football over them
--mountains?... Yeah... Coach woulda put me in fourth quarter, we would've been
--state champions. No doubt. No doubt in my mind.
Ha! My life is complete, now that Napoleon Dynamite has been included in comments...:w00t:
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
September 3, 2009 at 6:04 pm
By the way Steve, my boss refused to buy the Math Clock you found on thinkgeek.com, so I was forced to print a screenshot, cut a hole out of the middle so you could still see the hands moving, and tape it to the clock in our unit.
I also proposed that anybody who complained that they couldn't tell the time be summarily fired, but that one didn't go over well...:-D
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
September 3, 2009 at 6:53 pm
CirquedeSQLeil (9/3/2009)
hmmm - guess he didn't want to post his codedoes that mean he has learned how to research the issue and troubleshoot?
He was probably using sp_Space used or some code that looks at sysIndexes and DBCC UPDATEUSAGE fixes some sins that occur there.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 3, 2009 at 9:22 pm
Steve Jones - Editor (9/3/2009)
Paul, I'd raise this issue again, and actually I'll make a note of it. They do update things, but it's a big book. I'm not surprised things fall through the cracks.
Thanks Steve. It may or may not surprise you to know that I quite happily go on about this to anyone who'll listen - so no problem with taking it up with them again 🙂
Actually, I'd just rather they fixed it so that PARSENAME is deterministic. Given that CHARINDEX and SUBSTRING are deterministic, it makes no sense to me (you can write PARSENAME using those functions). PARSENAME is so handy (especially with IP addresses) so it frustrates me that I can't persist a computed column that uses it, or schema-bind a function that relies on it 🙁
I've added a Connect item for this.
The full script was too long :blush: so the Connect contains a cut-down version. This is the full thing:
USE [tempdb];
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
GO
IF (OBJECT_ID(N'tempdb..[#CE6130F7-1355-4803-929E-C3AF32090482]', N'U') IS NOT NULL)
BEGIN
DROP TABLE [#CE6130F7-1355-4803-929E-C3AF32090482];
END;
GO
-- Create table using PARSENAME in computed columns
CREATE TABLE [#CE6130F7-1355-4803-929E-C3AF32090482]
(
ip_address NVARCHAR(15) NOT NULL PRIMARY KEY,
octet1 AS PARSENAME(ip_address, 4),
octet2 AS PARSENAME(ip_address, 3),
octet3 AS PARSENAME(ip_address, 2),
octet4 AS PARSENAME(ip_address, 1)
);
-- Add a row
INSERT [#CE6130F7-1355-4803-929E-C3AF32090482] (ip_address) VALUES (N'12.34.56.789');
-- Show the contents
SELECT ip_address,
octet1,
octet2,
octet3,
octet4
FROM [#CE6130F7-1355-4803-929E-C3AF32090482];
-- Returns zero
SELECT column_is_deterministic =
COLUMNPROPERTY
(
OBJECT_ID(N'tempdb..[#CE6130F7-1355-4803-929E-C3AF32090482]', N'U'),
N'octet1',
N'IsDeterministic'
);
-- Tidy up
IF (OBJECT_ID(N'tempdb..[#CE6130F7-1355-4803-929E-C3AF32090482]', N'U') IS NOT NULL)
BEGIN
DROP TABLE [#CE6130F7-1355-4803-929E-C3AF32090482];
END;
-- End repro
GO
--
-- A workaround is to use CHARINDEX and SUBSTRING
-- (which are both deterministic)
--
USE [tempdb];
IF (OBJECT_ID(N'[dbo].[itfn_IPv4_Octets]', N'IF') IS NOT NULL)
BEGIN
DROP FUNCTION [dbo].[itfn_IPv4_Octets];
END;
IF (OBJECT_ID(N'[dbo].[itfn_IPv4_Octets_PN]', N'IF') IS NOT NULL)
BEGIN
DROP FUNCTION [dbo].[itfn_IPv4_Octets_PN];
END;
GO
-- Inline TVF to split an IP address into octets
-- using CHARINDEX and SUBSTRING (deterministic)
CREATE FUNCTION [dbo].[itfn_IPv4_Octets] (@IP NVARCHAR(15))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT octet1 = CASE WHEN LEN(@IP) - LEN(REPLACE(@IP, N'.', N'')) = 3 THEN ISNULL(CONVERT(NVARCHAR(3), SUBSTRING(@IP, 1, (CHARINDEX(N'.', @IP, 1)) - 1)), N'') ELSE N'' END,
octet2 = CASE WHEN LEN(@IP) - LEN(REPLACE(@IP, N'.', N'')) = 3 THEN ISNULL(CONVERT(NVARCHAR(3), SUBSTRING(@IP, (CHARINDEX(N'.', @IP, 1)) + 1, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) - (CHARINDEX(N'.', @IP, 1)) - 1)), N'') ELSE N'' END,
octet3 = CASE WHEN LEN(@IP) - LEN(REPLACE(@IP, N'.', N'')) = 3 THEN ISNULL(CONVERT(NVARCHAR(3), SUBSTRING(@IP, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) + 1, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) + 1)) - (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) - 1)), N'') ELSE N'' END,
octet4 = CASE WHEN LEN(@IP) - LEN(REPLACE(@IP, N'.', N'')) = 3 THEN ISNULL(CONVERT(NVARCHAR(3), SUBSTRING(@IP, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) + 1)) + 1, LEN(@IP) - (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) + 1)))), N'') ELSE N'' END;
GO
-- Inline TVF to split an IP address into octets
-- using PARSENAME (non-deterministic)
CREATE FUNCTION [dbo].[itfn_IPv4_Octets_PN] (@IP NVARCHAR(15))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT octet1 = CASE WHEN LEN(@IP) - LEN(REPLACE(@IP, N'.', N'')) = 3 THEN PARSENAME(@IP, 4) ELSE N'' END,
octet2 = CASE WHEN LEN(@IP) - LEN(REPLACE(@IP, N'.', N'')) = 3 THEN PARSENAME(@IP, 3) ELSE N'' END,
octet3 = CASE WHEN LEN(@IP) - LEN(REPLACE(@IP, N'.', N'')) = 3 THEN PARSENAME(@IP, 2) ELSE N'' END,
octet4 = CASE WHEN LEN(@IP) - LEN(REPLACE(@IP, N'.', N'')) = 3 THEN PARSENAME(@IP, 1) ELSE N'' END;
GO
-- Test it
DECLARE @T
TABLE (IP NVARCHAR(15) NOT NULL PRIMARY KEY);
INSERT @T (IP) VALUES (N'1.2.3.4');
SELECT T.IP,
Octets.octet1,
Octets.octet2,
Octets.octet3,
Octets.octet4
FROM @T T
CROSS
APPLY [dbo].[itfn_IPv4_Octets] (IP) Octets;
SELECT T.IP,
Octets.octet1,
Octets.octet2,
Octets.octet3,
Octets.octet4
FROM @T T
CROSS
APPLY [dbo].[itfn_IPv4_Octets_PN] (IP) Octets;
-- Returns 1
SELECT function_is_deterministic =
OBJECTPROPERTYEX
(
OBJECT_ID(N'[dbo].[itfn_IPv4_Octets]', N'IF'),
N'IsDeterministic'
);
-- Returns 0
SELECT function_is_deterministic =
OBJECTPROPERTYEX
(
OBJECT_ID(N'[dbo].[itfn_IPv4_Octets_PN]', N'IF'),
N'IsDeterministic'
);
-- Tidy up
IF (OBJECT_ID(N'[dbo].[itfn_IPv4_Octets]', N'IF') IS NOT NULL)
BEGIN
DROP FUNCTION [dbo].[itfn_IPv4_Octets];
END;
IF (OBJECT_ID(N'[dbo].[itfn_IPv4_Octets_PN]', N'IF') IS NOT NULL)
BEGIN
DROP FUNCTION [dbo].[itfn_IPv4_Octets_PN];
END;
-- End workaround
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 4, 2009 at 1:08 am
Jeff Moden (9/3/2009)
He was probably using sp_Space used or some code that looks at sysIndexes and DBCC UPDATEUSAGE fixes some sins that occur there.
The code he initially posted (before editing it out) just referred to DBCC SQLPERF(LOGSPACE) as far as I recall.
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
September 4, 2009 at 8:45 am
GilaMonster (9/4/2009)
Jeff Moden (9/3/2009)
He was probably using sp_Space used or some code that looks at sysIndexes and DBCC UPDATEUSAGE fixes some sins that occur there.The code he initially posted (before editing it out) just referred to DBCC SQLPERF(LOGSPACE) as far as I recall.
yep it was.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 4, 2009 at 4:04 pm
It is way too quiet at the watercooler for a Friday evening (SA time). Where are you Guys and Gals? Came back from Baseball practice three hours ago and thought my mailbox would be overflowing by now! Anyhow, strange that the sore muscles I had from Wednesday's session seem to have magically (almost) disappeared after another session tonight, and not an easy one for that matter. Coach must have been in the army in his younger days. Is that sudden recovery normal? Or is that due to the few beers we had afterwards 😛 Anyhow, it is feeling good to be physically tired instead of just mentally for a change. Looking forward to the first friendly game of the season with our neighbouring club on Sunday.
Just thought I'd share this, don't take me serious... 🙂
Viewing 15 posts - 7,861 through 7,875 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply