Newbie Question. Please help.
I need to change the computer name on the sql server 2000.
What steps are involved after the computer name change.
Thanks a bunch.If you use a domain account that has restricted login ability, you will need to add that account to the new computername to be able to login in AD.
You will need to run sp_dropserver and sp_addserver (look in BOL for syntax)
If you created jobs while the PC was named the old name you will need to update the originating server name in sysjobs to your new PC name.
HTH
Showing posts with label involved. Show all posts
Showing posts with label involved. Show all posts
Sunday, March 25, 2012
Saturday, February 25, 2012
Complex Query
Need to create a SELECT statement which is above my skills. There are two
tables involved, both are shown below. Table 1 stores messages to be
delivered to a user on a web page. Messages can be sent to a specific set o
f
users by setting BlockList to false and adding each user to Table 2 or to al
l
users by setting BlockList to true and adding no users to Table 2. Messages
can be active until acknowledged (EndDate is null) or to a specific EndDate.
The query must return all active messages for a specific user name (the only
parameter). A message should be returned if BlockList is false and the user
is listed, or BlockList is true and the user is not listed. In addition, if
there is an end date it must not be passed and if BlockList is false (meanin
g
the user list was established as a list of recipients) then the Recieved dat
e
on Table 2 must be null indicating the user has not yet received the message
.
I am open to table architecture changes which might allow the same
information to be conveyed in a simpler manner if those more experienced
think that is better than writing a query to extract this information.
Table 1: IntranetMessages
_Column Name_ _Type_ _Allow Null_
MessageID (PK) uniqueidentifier No
Text nvarchar(MAX) No
URL nvarchar(MAX) No
Tooltip nvarchar(MAX) No
BlockList bit No
IssueDate DateTime No
EndDate DateTime Yes
Table 2: IntranetMessageRecipients
_Column Name_ _Type_ _Allow Null_
RecID (PK) uniqueidentifier No
MessageID (FK) uniqueidentifier No
Recipient nvarchar(MAX) No
Received datetime Yesyou'll need a reference from the IntranetMessageRecipients to the
IntranetMessages to achieve this, aka foreign key.
is this possible?
> _Column Name_ _Type_ _Allow Null_
RecID int No
> MessageID (PK) uniqueidentifier No
> Text nvarchar(MAX) No
> URL nvarchar(MAX) No
> Tooltip nvarchar(MAX) No
> BlockList bit No
> IssueDate DateTime No
> EndDate DateTime Yes
you could insert a default value for those messages that do not have a
recipient.
> Table 2: IntranetMessageRecipients
> _Column Name_ _Type_ _Allow Null_
> RecID (PK) uniqueidentifier No
> MessageID (FK) uniqueidentifier No
> Recipient nvarchar(MAX) No
> Received datetime Yes|||Sorry, I marked the MessageID field in the IntranetMessageRecipients table a
s
a Foreign Key but didn't explicitely say that it corresponds to the MessageI
D
(PK) in the IntranetMessages table. That is how it is setup, however, if
that is what you mean.
"Gerard" wrote:
> you'll need a reference from the IntranetMessageRecipients to the
> IntranetMessages to achieve this, aka foreign key.
> is this possible?
>
> RecID int No
> you could insert a default value for those messages that do not have a
> recipient.
>
>
>|||SELECT im.MessageID, im.Text, im.URL, im.Tooltip, @.passedInUser as Recipient
FROM IntranetMessages im
LEFT OUTER JOIN
IntranetMessageRecipients imr
ON im.MessageID = imr.MessageID
AND imr.Received IS NULL
WHERE CASE WHEN im.BlockList = 1 THEN Recipient ELSE @.passedInUser END =
@.passedInUser
AND im.EndDate IS NULL
A couple of thoughts on your schema. I would be careful with the use of
uniqueidentifiers as your primary keys. For several reasons:
1) I hope that you have the primary key as a clustered index. It is a good
rule of thumb to always have a clustered index
2) Uniqueidentifiers are typically bad choices as clustered indexes since
they are not ever increasing. This causes your table to become fragmented
quickly. If you use uniqueidentifiers as your clustered primary key, you
should make sure that you are rebuilding the index regularly.
Beyond that, your schema is not normalized since you could have the same
username in your intranetMessage table many times. It would make this a bit
cleaner if you had a table with all users that is referenced by the
Recipients table.
But, I think that my query should do the trick for you.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"MKing" wrote:
> Need to create a SELECT statement which is above my skills. There are two
> tables involved, both are shown below. Table 1 stores messages to be
> delivered to a user on a web page. Messages can be sent to a specific set
of
> users by setting BlockList to false and adding each user to Table 2 or to
all
> users by setting BlockList to true and adding no users to Table 2. Messag
es
> can be active until acknowledged (EndDate is null) or to a specific EndDat
e.
> The query must return all active messages for a specific user name (the on
ly
> parameter). A message should be returned if BlockList is false and the us
er
> is listed, or BlockList is true and the user is not listed. In addition,
if
> there is an end date it must not be passed and if BlockList is false (mean
ing
> the user list was established as a list of recipients) then the Recieved d
ate
> on Table 2 must be null indicating the user has not yet received the messa
ge.
> I am open to table architecture changes which might allow the same
> information to be conveyed in a simpler manner if those more experienced
> think that is better than writing a query to extract this information.
> Table 1: IntranetMessages
> _Column Name_ _Type_ _Allow Null_
> MessageID (PK) uniqueidentifier No
> Text nvarchar(MAX) No
> URL nvarchar(MAX) No
> Tooltip nvarchar(MAX) No
> BlockList bit No
> IssueDate DateTime No
> EndDate DateTime Yes
> Table 2: IntranetMessageRecipients
> _Column Name_ _Type_ _Allow Null_
> RecID (PK) uniqueidentifier No
> MessageID (FK) uniqueidentifier No
> Recipient nvarchar(MAX) No
> Received datetime Yes
>
>|||sorry misunderstanding on my part
maybe something like this will work (not tested)
select * from IntranetMessages
inner join IntranetMessageRecipients on
IntranetMessageRecipients.MessageID = IntranetMessages .MessageID
where
(BlockList = 0 and IntranetMessageRecipients.RecID = @.recp) or
(BlockList = 1 and IntranetMessageRecipients.RecID <> @.recp)
regards
Gerard|||Thanks Ryan, I will try this. Thanks for the tip on uniqueidentifiers... I
don't know what a clustered index is, but that's for me to look up and your
point about usernames is well taken.
"Ryan Powers" wrote:
> SELECT im.MessageID, im.Text, im.URL, im.Tooltip, @.passedInUser as Recipie
nt
> FROM IntranetMessages im
> LEFT OUTER JOIN
> IntranetMessageRecipients imr
> ON im.MessageID = imr.MessageID
> AND imr.Received IS NULL
> WHERE CASE WHEN im.BlockList = 1 THEN Recipient ELSE @.passedInUser END =
> @.passedInUser
> AND im.EndDate IS NULL
> A couple of thoughts on your schema. I would be careful with the use of
> uniqueidentifiers as your primary keys. For several reasons:
> 1) I hope that you have the primary key as a clustered index. It is a goo
d
> rule of thumb to always have a clustered index
> 2) Uniqueidentifiers are typically bad choices as clustered indexes since
> they are not ever increasing. This causes your table to become fragmented
> quickly. If you use uniqueidentifiers as your clustered primary key, you
> should make sure that you are rebuilding the index regularly.
> Beyond that, your schema is not normalized since you could have the same
> username in your intranetMessage table many times. It would make this a b
it
> cleaner if you had a table with all users that is referenced by the
> Recipients table.
> But, I think that my query should do the trick for you.
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "MKing" wrote:
>|||Let me know if it works.
Also, if you need me to point you to a couple of sites about Clustered
indexes and tuning, let me know.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"MKing" wrote:
> Thanks Ryan, I will try this. Thanks for the tip on uniqueidentifiers...
I
> don't know what a clustered index is, but that's for me to look up and you
r
> point about usernames is well taken.
> "Ryan Powers" wrote:
>|||If you don't mind a few references are always helpful. I am just getting my
feet wet in SQL Server so I have a lot to learn.
Your query got me 90% of the way there and once I read up on CASE and LEFT
OUTER JOIN statements to see what was going on I got it to do what I needed.
The end result follows:
SELECT im.MessageID, im.BlockList, imr.Recipient
FROM dbo.IntranetMessages AS im LEFT OUTER JOIN
dbo.IntranetMessageRecipients AS imr ON im.MessageID =
imr.MessageID AND imr.Received IS NULL
WHERE (im.EndDate IS NULL OR
im.EndDate > GETDATE()) AND (CASE WHEN im.BlockList =
0 THEN (CASE WHEN Recipient = 'odehengineers\kingm' THEN 1 ELSE 0 END)
ELSE (CASE WHEN (Recipient IS NULL OR
Recipient <> 'odehengineers\kingm') THEN 1 ELSE 0 END)
END = 1)
"Ryan Powers" wrote:
> Let me know if it works.
> Also, if you need me to point you to a couple of sites about Clustered
> indexes and tuning, let me know.
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "MKing" wrote:
>|||Sorry, I had replaced the parameters for testing. The real version is:
SELECT im.MessageID
FROM dbo.IntranetMessages AS im LEFT OUTER JOIN
dbo.IntranetMessageRecipients AS imr ON
im.MessageID = imr.MessageID AND imr.Received IS NULL
WHERE (im.EndDate IS NULL OR
im.EndDate > GETDATE()) AND (CASE WHEN
im.BlockList = 0 THEN (CASE WHEN Recipient = @.USER THEN 1 ELSE 0 END)
ELSE (CASE WHEN (Recipient IS NULL OR
Recipient <> @.USER) THEN 1 ELSE 0 END) END = 1)
"MKing" wrote:
> If you don't mind a few references are always helpful. I am just getting
my
> feet wet in SQL Server so I have a lot to learn.
> Your query got me 90% of the way there and once I read up on CASE and LEFT
> OUTER JOIN statements to see what was going on I got it to do what I neede
d.
> The end result follows:
> SELECT im.MessageID, im.BlockList, imr.Recipient
> FROM dbo.IntranetMessages AS im LEFT OUTER JOIN
> dbo.IntranetMessageRecipients AS imr ON im.MessageID
=
> imr.MessageID AND imr.Received IS NULL
> WHERE (im.EndDate IS NULL OR
> im.EndDate > GETDATE()) AND (CASE WHEN im.BlockList
=
> 0 THEN (CASE WHEN Recipient = 'odehengineers\kingm' THEN 1 ELSE 0 END)
> ELSE (CASE WHEN (Recipient IS NULL OR
> Recipient <> 'odehengineers\kingm') THEN 1 ELSE 0 EN
D)
> END = 1)
> "Ryan Powers" wrote:
>|||Glad you got it to work.
I was trying to figure out why what I gave you did not work right and I see
two things. Because I think you can do something a bit cleaner than your
final solution.
1) It looks like you are saying match the recipient when blocklist is 0
instead of 1. Can you explain that (just for my curiousity)? Is it true tha
t
you want to look the recipient up when BlockList = 0? Is it also true that
the message will have no rows in the Recipient table when BlockList = 1?
2) I failed to include rows with end date later than today.
As far as references go, I prefer watching presentations/ webcasts. There
is a great one by Kimberly Tripp on index best practices that would give you
a great background on clustered indexes. It is focused on SQL 2005, but the
concepts transcend the releases. You can find it here.
http://www.microsoft.com/uk/technet...aspx?videoid=29
Beyond that, here are a couple good articles.
http://www.sql-server-performance.c..._structures.asp
http://www.sql-server-performance.c...red_indexes.asp
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"MKing" wrote:
> Sorry, I had replaced the parameters for testing. The real version is:
> SELECT im.MessageID
> FROM dbo.IntranetMessages AS im LEFT OUTER JOIN
> dbo.IntranetMessageRecipients AS imr ON
> im.MessageID = imr.MessageID AND imr.Received IS NULL
> WHERE (im.EndDate IS NULL OR
> im.EndDate > GETDATE()) AND (CASE WHEN
> im.BlockList = 0 THEN (CASE WHEN Recipient = @.USER THEN 1 ELSE 0 END)
> ELSE (CASE WHEN (Recipient IS NULL OR
> Recipient <> @.USER) THEN 1 ELSE 0 END) END = 1
)
> "MKing" wrote:
>
tables involved, both are shown below. Table 1 stores messages to be
delivered to a user on a web page. Messages can be sent to a specific set o
f
users by setting BlockList to false and adding each user to Table 2 or to al
l
users by setting BlockList to true and adding no users to Table 2. Messages
can be active until acknowledged (EndDate is null) or to a specific EndDate.
The query must return all active messages for a specific user name (the only
parameter). A message should be returned if BlockList is false and the user
is listed, or BlockList is true and the user is not listed. In addition, if
there is an end date it must not be passed and if BlockList is false (meanin
g
the user list was established as a list of recipients) then the Recieved dat
e
on Table 2 must be null indicating the user has not yet received the message
.
I am open to table architecture changes which might allow the same
information to be conveyed in a simpler manner if those more experienced
think that is better than writing a query to extract this information.
Table 1: IntranetMessages
_Column Name_ _Type_ _Allow Null_
MessageID (PK) uniqueidentifier No
Text nvarchar(MAX) No
URL nvarchar(MAX) No
Tooltip nvarchar(MAX) No
BlockList bit No
IssueDate DateTime No
EndDate DateTime Yes
Table 2: IntranetMessageRecipients
_Column Name_ _Type_ _Allow Null_
RecID (PK) uniqueidentifier No
MessageID (FK) uniqueidentifier No
Recipient nvarchar(MAX) No
Received datetime Yesyou'll need a reference from the IntranetMessageRecipients to the
IntranetMessages to achieve this, aka foreign key.
is this possible?
> _Column Name_ _Type_ _Allow Null_
RecID int No
> MessageID (PK) uniqueidentifier No
> Text nvarchar(MAX) No
> URL nvarchar(MAX) No
> Tooltip nvarchar(MAX) No
> BlockList bit No
> IssueDate DateTime No
> EndDate DateTime Yes
you could insert a default value for those messages that do not have a
recipient.
> Table 2: IntranetMessageRecipients
> _Column Name_ _Type_ _Allow Null_
> RecID (PK) uniqueidentifier No
> MessageID (FK) uniqueidentifier No
> Recipient nvarchar(MAX) No
> Received datetime Yes|||Sorry, I marked the MessageID field in the IntranetMessageRecipients table a
s
a Foreign Key but didn't explicitely say that it corresponds to the MessageI
D
(PK) in the IntranetMessages table. That is how it is setup, however, if
that is what you mean.
"Gerard" wrote:
> you'll need a reference from the IntranetMessageRecipients to the
> IntranetMessages to achieve this, aka foreign key.
> is this possible?
>
> RecID int No
> you could insert a default value for those messages that do not have a
> recipient.
>
>
>|||SELECT im.MessageID, im.Text, im.URL, im.Tooltip, @.passedInUser as Recipient
FROM IntranetMessages im
LEFT OUTER JOIN
IntranetMessageRecipients imr
ON im.MessageID = imr.MessageID
AND imr.Received IS NULL
WHERE CASE WHEN im.BlockList = 1 THEN Recipient ELSE @.passedInUser END =
@.passedInUser
AND im.EndDate IS NULL
A couple of thoughts on your schema. I would be careful with the use of
uniqueidentifiers as your primary keys. For several reasons:
1) I hope that you have the primary key as a clustered index. It is a good
rule of thumb to always have a clustered index
2) Uniqueidentifiers are typically bad choices as clustered indexes since
they are not ever increasing. This causes your table to become fragmented
quickly. If you use uniqueidentifiers as your clustered primary key, you
should make sure that you are rebuilding the index regularly.
Beyond that, your schema is not normalized since you could have the same
username in your intranetMessage table many times. It would make this a bit
cleaner if you had a table with all users that is referenced by the
Recipients table.
But, I think that my query should do the trick for you.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"MKing" wrote:
> Need to create a SELECT statement which is above my skills. There are two
> tables involved, both are shown below. Table 1 stores messages to be
> delivered to a user on a web page. Messages can be sent to a specific set
of
> users by setting BlockList to false and adding each user to Table 2 or to
all
> users by setting BlockList to true and adding no users to Table 2. Messag
es
> can be active until acknowledged (EndDate is null) or to a specific EndDat
e.
> The query must return all active messages for a specific user name (the on
ly
> parameter). A message should be returned if BlockList is false and the us
er
> is listed, or BlockList is true and the user is not listed. In addition,
if
> there is an end date it must not be passed and if BlockList is false (mean
ing
> the user list was established as a list of recipients) then the Recieved d
ate
> on Table 2 must be null indicating the user has not yet received the messa
ge.
> I am open to table architecture changes which might allow the same
> information to be conveyed in a simpler manner if those more experienced
> think that is better than writing a query to extract this information.
> Table 1: IntranetMessages
> _Column Name_ _Type_ _Allow Null_
> MessageID (PK) uniqueidentifier No
> Text nvarchar(MAX) No
> URL nvarchar(MAX) No
> Tooltip nvarchar(MAX) No
> BlockList bit No
> IssueDate DateTime No
> EndDate DateTime Yes
> Table 2: IntranetMessageRecipients
> _Column Name_ _Type_ _Allow Null_
> RecID (PK) uniqueidentifier No
> MessageID (FK) uniqueidentifier No
> Recipient nvarchar(MAX) No
> Received datetime Yes
>
>|||sorry misunderstanding on my part
maybe something like this will work (not tested)
select * from IntranetMessages
inner join IntranetMessageRecipients on
IntranetMessageRecipients.MessageID = IntranetMessages .MessageID
where
(BlockList = 0 and IntranetMessageRecipients.RecID = @.recp) or
(BlockList = 1 and IntranetMessageRecipients.RecID <> @.recp)
regards
Gerard|||Thanks Ryan, I will try this. Thanks for the tip on uniqueidentifiers... I
don't know what a clustered index is, but that's for me to look up and your
point about usernames is well taken.
"Ryan Powers" wrote:
> SELECT im.MessageID, im.Text, im.URL, im.Tooltip, @.passedInUser as Recipie
nt
> FROM IntranetMessages im
> LEFT OUTER JOIN
> IntranetMessageRecipients imr
> ON im.MessageID = imr.MessageID
> AND imr.Received IS NULL
> WHERE CASE WHEN im.BlockList = 1 THEN Recipient ELSE @.passedInUser END =
> @.passedInUser
> AND im.EndDate IS NULL
> A couple of thoughts on your schema. I would be careful with the use of
> uniqueidentifiers as your primary keys. For several reasons:
> 1) I hope that you have the primary key as a clustered index. It is a goo
d
> rule of thumb to always have a clustered index
> 2) Uniqueidentifiers are typically bad choices as clustered indexes since
> they are not ever increasing. This causes your table to become fragmented
> quickly. If you use uniqueidentifiers as your clustered primary key, you
> should make sure that you are rebuilding the index regularly.
> Beyond that, your schema is not normalized since you could have the same
> username in your intranetMessage table many times. It would make this a b
it
> cleaner if you had a table with all users that is referenced by the
> Recipients table.
> But, I think that my query should do the trick for you.
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "MKing" wrote:
>|||Let me know if it works.
Also, if you need me to point you to a couple of sites about Clustered
indexes and tuning, let me know.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"MKing" wrote:
> Thanks Ryan, I will try this. Thanks for the tip on uniqueidentifiers...
I
> don't know what a clustered index is, but that's for me to look up and you
r
> point about usernames is well taken.
> "Ryan Powers" wrote:
>|||If you don't mind a few references are always helpful. I am just getting my
feet wet in SQL Server so I have a lot to learn.
Your query got me 90% of the way there and once I read up on CASE and LEFT
OUTER JOIN statements to see what was going on I got it to do what I needed.
The end result follows:
SELECT im.MessageID, im.BlockList, imr.Recipient
FROM dbo.IntranetMessages AS im LEFT OUTER JOIN
dbo.IntranetMessageRecipients AS imr ON im.MessageID =
imr.MessageID AND imr.Received IS NULL
WHERE (im.EndDate IS NULL OR
im.EndDate > GETDATE()) AND (CASE WHEN im.BlockList =
0 THEN (CASE WHEN Recipient = 'odehengineers\kingm' THEN 1 ELSE 0 END)
ELSE (CASE WHEN (Recipient IS NULL OR
Recipient <> 'odehengineers\kingm') THEN 1 ELSE 0 END)
END = 1)
"Ryan Powers" wrote:
> Let me know if it works.
> Also, if you need me to point you to a couple of sites about Clustered
> indexes and tuning, let me know.
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "MKing" wrote:
>|||Sorry, I had replaced the parameters for testing. The real version is:
SELECT im.MessageID
FROM dbo.IntranetMessages AS im LEFT OUTER JOIN
dbo.IntranetMessageRecipients AS imr ON
im.MessageID = imr.MessageID AND imr.Received IS NULL
WHERE (im.EndDate IS NULL OR
im.EndDate > GETDATE()) AND (CASE WHEN
im.BlockList = 0 THEN (CASE WHEN Recipient = @.USER THEN 1 ELSE 0 END)
ELSE (CASE WHEN (Recipient IS NULL OR
Recipient <> @.USER) THEN 1 ELSE 0 END) END = 1)
"MKing" wrote:
> If you don't mind a few references are always helpful. I am just getting
my
> feet wet in SQL Server so I have a lot to learn.
> Your query got me 90% of the way there and once I read up on CASE and LEFT
> OUTER JOIN statements to see what was going on I got it to do what I neede
d.
> The end result follows:
> SELECT im.MessageID, im.BlockList, imr.Recipient
> FROM dbo.IntranetMessages AS im LEFT OUTER JOIN
> dbo.IntranetMessageRecipients AS imr ON im.MessageID
=
> imr.MessageID AND imr.Received IS NULL
> WHERE (im.EndDate IS NULL OR
> im.EndDate > GETDATE()) AND (CASE WHEN im.BlockList
=
> 0 THEN (CASE WHEN Recipient = 'odehengineers\kingm' THEN 1 ELSE 0 END)
> ELSE (CASE WHEN (Recipient IS NULL OR
> Recipient <> 'odehengineers\kingm') THEN 1 ELSE 0 EN
D)
> END = 1)
> "Ryan Powers" wrote:
>|||Glad you got it to work.
I was trying to figure out why what I gave you did not work right and I see
two things. Because I think you can do something a bit cleaner than your
final solution.
1) It looks like you are saying match the recipient when blocklist is 0
instead of 1. Can you explain that (just for my curiousity)? Is it true tha
t
you want to look the recipient up when BlockList = 0? Is it also true that
the message will have no rows in the Recipient table when BlockList = 1?
2) I failed to include rows with end date later than today.
As far as references go, I prefer watching presentations/ webcasts. There
is a great one by Kimberly Tripp on index best practices that would give you
a great background on clustered indexes. It is focused on SQL 2005, but the
concepts transcend the releases. You can find it here.
http://www.microsoft.com/uk/technet...aspx?videoid=29
Beyond that, here are a couple good articles.
http://www.sql-server-performance.c..._structures.asp
http://www.sql-server-performance.c...red_indexes.asp
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"MKing" wrote:
> Sorry, I had replaced the parameters for testing. The real version is:
> SELECT im.MessageID
> FROM dbo.IntranetMessages AS im LEFT OUTER JOIN
> dbo.IntranetMessageRecipients AS imr ON
> im.MessageID = imr.MessageID AND imr.Received IS NULL
> WHERE (im.EndDate IS NULL OR
> im.EndDate > GETDATE()) AND (CASE WHEN
> im.BlockList = 0 THEN (CASE WHEN Recipient = @.USER THEN 1 ELSE 0 END)
> ELSE (CASE WHEN (Recipient IS NULL OR
> Recipient <> @.USER) THEN 1 ELSE 0 END) END = 1
)
> "MKing" wrote:
>
Sunday, February 19, 2012
Compiles / Sec on 2005
Hi All
I read somewhere that the compiles / sec performance counter should be used
to evaluate the risk involved in upgrating a SQL 2000 Server to a SQL 2005
server. That SQL 2005 is more CPU intensive on these tasks? Does anyone
have more documentation / information on the subject ?
Thanks
Elrond BishopWell since 2005 can compile at the statement level vs. the entire batch or
proc there can be considerable savings in recompile costs. It they are all
compiles that means none of the plans are being reused. In that case you may
be able to get some help with the Force Parameterization option or the use
of plan guides. See here for more details:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Elrond Bishop" <billgates@.microsoft.com> wrote in message
news:ODJKGvoBIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Hi All
> I read somewhere that the compiles / sec performance counter should be
> used to evaluate the risk involved in upgrating a SQL 2000 Server to a SQL
> 2005 server. That SQL 2005 is more CPU intensive on these tasks? Does
> anyone have more documentation / information on the subject ?
> Thanks
> Elrond Bishop|||The database has a lot of "Dynamic" SQL coming into the database from VB
strings executed through the conneciton object.. Bad programming practice I
know but very little we can do about it at the moment.
Should I be worried about a higher CPU usage in that case ?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23ZmiGEpBIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Well since 2005 can compile at the statement level vs. the entire batch or
> proc there can be considerable savings in recompile costs. It they are all
> compiles that means none of the plans are being reused. In that case you
> may be able to get some help with the Force Parameterization option or the
> use of plan guides. See here for more details:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Elrond Bishop" <billgates@.microsoft.com> wrote in message
> news:ODJKGvoBIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi All
>> I read somewhere that the compiles / sec performance counter should be
>> used to evaluate the risk involved in upgrating a SQL 2000 Server to a
>> SQL 2005 server. That SQL 2005 is more CPU intensive on these tasks?
>> Does anyone have more documentation / information on the subject ?
>> Thanks
>> Elrond Bishop
>|||Not only CPU, but also RAM and Network...
You may want to analyse that using SQL Server Profiler (make a workload file
and trace conditions of your queries) and Database Tuning Advisor for
getting some useful recommendation on your query commands (using that
workload file)?
From your message I understand that you are not with your current querying
solution but I just wanted to stress that it would be much much better using
SPs for quering against your database from your VB apps. Using SPs, you'll
not be able to change your hard-coded SPs for minor code changings (which
does not affect datasets that return to your app) and it would be more
secure and fast (SPs are much faster and better than ad hocs)
--
Ekrem Önsoy
"Elrond Bishop" <billgates@.microsoft.com> wrote in message
news:ORurkmpBIHA.3900@.TK2MSFTNGP02.phx.gbl...
> The database has a lot of "Dynamic" SQL coming into the database from VB
> strings executed through the conneciton object.. Bad programming practice
> I know but very little we can do about it at the moment.
> Should I be worried about a higher CPU usage in that case ?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23ZmiGEpBIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Well since 2005 can compile at the statement level vs. the entire batch
>> or proc there can be considerable savings in recompile costs. It they are
>> all compiles that means none of the plans are being reused. In that case
>> you may be able to get some help with the Force Parameterization option
>> or the use of plan guides. See here for more details:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Elrond Bishop" <billgates@.microsoft.com> wrote in message
>> news:ODJKGvoBIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi All
>> I read somewhere that the compiles / sec performance counter should be
>> used to evaluate the risk involved in upgrating a SQL 2000 Server to a
>> SQL 2005 server. That SQL 2005 is more CPU intensive on these tasks?
>> Does anyone have more documentation / information on the subject ?
>> Thanks
>> Elrond Bishop
>|||I understand the importance of stored procedures. just dont have a choice in
the matter at the moment. there are +- 80 different VB applicaitons
countless lines of VBA and other methods of accessing the data. a bit of a
mess, but it was inherited. There is a project underway to move the
applications to SP's but thats going to take a while. there are also loads
and loads of SP based queries, so compiles may have been coming from that as
well. looking at an average of 3000 - 8000 connections during the day, 500
gig database.
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:E474511D-11F6-4CA3-B459-A4793192154A@.microsoft.com...
> Not only CPU, but also RAM and Network...
> You may want to analyse that using SQL Server Profiler (make a workload
> file and trace conditions of your queries) and Database Tuning Advisor for
> getting some useful recommendation on your query commands (using that
> workload file)?
> From your message I understand that you are not with your current querying
> solution but I just wanted to stress that it would be much much better
> using SPs for quering against your database from your VB apps. Using SPs,
> you'll not be able to change your hard-coded SPs for minor code changings
> (which does not affect datasets that return to your app) and it would be
> more secure and fast (SPs are much faster and better than ad hocs)
> --
> Ekrem Önsoy
>
> "Elrond Bishop" <billgates@.microsoft.com> wrote in message
> news:ORurkmpBIHA.3900@.TK2MSFTNGP02.phx.gbl...
>> The database has a lot of "Dynamic" SQL coming into the database from VB
>> strings executed through the conneciton object.. Bad programming practice
>> I know but very little we can do about it at the moment.
>> Should I be worried about a higher CPU usage in that case ?
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23ZmiGEpBIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Well since 2005 can compile at the statement level vs. the entire batch
>> or proc there can be considerable savings in recompile costs. It they
>> are all compiles that means none of the plans are being reused. In that
>> case you may be able to get some help with the Force Parameterization
>> option or the use of plan guides. See here for more details:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Elrond Bishop" <billgates@.microsoft.com> wrote in message
>> news:ODJKGvoBIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi All
>> I read somewhere that the compiles / sec performance counter should be
>> used to evaluate the risk involved in upgrating a SQL 2000 Server to a
>> SQL 2005 server. That SQL 2005 is more CPU intensive on these tasks?
>> Does anyone have more documentation / information on the subject ?
>> Thanks
>> Elrond Bishop
>>
>|||SQL 2005 will not increase the compile rates if they are already not reusing
the plans in 2000. It may in fact decrease it especially if you utilize some
of the features I mentioned earlier. But having said that the engine is
always evolving and the only way to know for sure is to test it under your
real life conditions. But the solution is really to fix the way the app
makes the calls so that you either use sps or format the calls so that they
can be reused via autoparameterizaion or sp_executesql.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Elrond Bishop" <billgates@.microsoft.com> wrote in message
news:ORurkmpBIHA.3900@.TK2MSFTNGP02.phx.gbl...
> The database has a lot of "Dynamic" SQL coming into the database from VB
> strings executed through the conneciton object.. Bad programming practice
> I know but very little we can do about it at the moment.
> Should I be worried about a higher CPU usage in that case ?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23ZmiGEpBIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Well since 2005 can compile at the statement level vs. the entire batch
>> or proc there can be considerable savings in recompile costs. It they are
>> all compiles that means none of the plans are being reused. In that case
>> you may be able to get some help with the Force Parameterization option
>> or the use of plan guides. See here for more details:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Elrond Bishop" <billgates@.microsoft.com> wrote in message
>> news:ODJKGvoBIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi All
>> I read somewhere that the compiles / sec performance counter should be
>> used to evaluate the risk involved in upgrating a SQL 2000 Server to a
>> SQL 2005 server. That SQL 2005 is more CPU intensive on these tasks?
>> Does anyone have more documentation / information on the subject ?
>> Thanks
>> Elrond Bishop
>|||With a bunch of dynamic sql it is quite possible you could very much benefit
from FORCED PARAMETERIZATION setting in 2005, as Andrew mentioned. Dynamic
sql that does lots of statement calls where it simply changes the values in
one or more WHERE clause lines is a great example of code that would
benefit. The first execution would be parameterized and the (hopefully
index seek/bookmark lookup) query plan cached. then as the code looped
through the remaining where clause values (such as EmpID = 1, EmpID = 2 ...)
the cached query plan will be reused - saving a lot of effort on compiles.
I have seen this very thing result in 30-40% throughput inprovement at one
of my clients.
"Elrond Bishop" <billgates@.microsoft.com> wrote in message
news:ORurkmpBIHA.3900@.TK2MSFTNGP02.phx.gbl...
> The database has a lot of "Dynamic" SQL coming into the database from VB
> strings executed through the conneciton object.. Bad programming practice
> I know but very little we can do about it at the moment.
> Should I be worried about a higher CPU usage in that case ?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23ZmiGEpBIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Well since 2005 can compile at the statement level vs. the entire batch
>> or proc there can be considerable savings in recompile costs. It they are
>> all compiles that means none of the plans are being reused. In that case
>> you may be able to get some help with the Force Parameterization option
>> or the use of plan guides. See here for more details:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Elrond Bishop" <billgates@.microsoft.com> wrote in message
>> news:ODJKGvoBIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi All
>> I read somewhere that the compiles / sec performance counter should be
>> used to evaluate the risk involved in upgrating a SQL 2000 Server to a
>> SQL 2005 server. That SQL 2005 is more CPU intensive on these tasks?
>> Does anyone have more documentation / information on the subject ?
>> Thanks
>> Elrond Bishop
>
I read somewhere that the compiles / sec performance counter should be used
to evaluate the risk involved in upgrating a SQL 2000 Server to a SQL 2005
server. That SQL 2005 is more CPU intensive on these tasks? Does anyone
have more documentation / information on the subject ?
Thanks
Elrond BishopWell since 2005 can compile at the statement level vs. the entire batch or
proc there can be considerable savings in recompile costs. It they are all
compiles that means none of the plans are being reused. In that case you may
be able to get some help with the Force Parameterization option or the use
of plan guides. See here for more details:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Elrond Bishop" <billgates@.microsoft.com> wrote in message
news:ODJKGvoBIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Hi All
> I read somewhere that the compiles / sec performance counter should be
> used to evaluate the risk involved in upgrating a SQL 2000 Server to a SQL
> 2005 server. That SQL 2005 is more CPU intensive on these tasks? Does
> anyone have more documentation / information on the subject ?
> Thanks
> Elrond Bishop|||The database has a lot of "Dynamic" SQL coming into the database from VB
strings executed through the conneciton object.. Bad programming practice I
know but very little we can do about it at the moment.
Should I be worried about a higher CPU usage in that case ?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23ZmiGEpBIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Well since 2005 can compile at the statement level vs. the entire batch or
> proc there can be considerable savings in recompile costs. It they are all
> compiles that means none of the plans are being reused. In that case you
> may be able to get some help with the Force Parameterization option or the
> use of plan guides. See here for more details:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Elrond Bishop" <billgates@.microsoft.com> wrote in message
> news:ODJKGvoBIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi All
>> I read somewhere that the compiles / sec performance counter should be
>> used to evaluate the risk involved in upgrating a SQL 2000 Server to a
>> SQL 2005 server. That SQL 2005 is more CPU intensive on these tasks?
>> Does anyone have more documentation / information on the subject ?
>> Thanks
>> Elrond Bishop
>|||Not only CPU, but also RAM and Network...
You may want to analyse that using SQL Server Profiler (make a workload file
and trace conditions of your queries) and Database Tuning Advisor for
getting some useful recommendation on your query commands (using that
workload file)?
From your message I understand that you are not with your current querying
solution but I just wanted to stress that it would be much much better using
SPs for quering against your database from your VB apps. Using SPs, you'll
not be able to change your hard-coded SPs for minor code changings (which
does not affect datasets that return to your app) and it would be more
secure and fast (SPs are much faster and better than ad hocs)
--
Ekrem Önsoy
"Elrond Bishop" <billgates@.microsoft.com> wrote in message
news:ORurkmpBIHA.3900@.TK2MSFTNGP02.phx.gbl...
> The database has a lot of "Dynamic" SQL coming into the database from VB
> strings executed through the conneciton object.. Bad programming practice
> I know but very little we can do about it at the moment.
> Should I be worried about a higher CPU usage in that case ?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23ZmiGEpBIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Well since 2005 can compile at the statement level vs. the entire batch
>> or proc there can be considerable savings in recompile costs. It they are
>> all compiles that means none of the plans are being reused. In that case
>> you may be able to get some help with the Force Parameterization option
>> or the use of plan guides. See here for more details:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Elrond Bishop" <billgates@.microsoft.com> wrote in message
>> news:ODJKGvoBIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi All
>> I read somewhere that the compiles / sec performance counter should be
>> used to evaluate the risk involved in upgrating a SQL 2000 Server to a
>> SQL 2005 server. That SQL 2005 is more CPU intensive on these tasks?
>> Does anyone have more documentation / information on the subject ?
>> Thanks
>> Elrond Bishop
>|||I understand the importance of stored procedures. just dont have a choice in
the matter at the moment. there are +- 80 different VB applicaitons
countless lines of VBA and other methods of accessing the data. a bit of a
mess, but it was inherited. There is a project underway to move the
applications to SP's but thats going to take a while. there are also loads
and loads of SP based queries, so compiles may have been coming from that as
well. looking at an average of 3000 - 8000 connections during the day, 500
gig database.
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:E474511D-11F6-4CA3-B459-A4793192154A@.microsoft.com...
> Not only CPU, but also RAM and Network...
> You may want to analyse that using SQL Server Profiler (make a workload
> file and trace conditions of your queries) and Database Tuning Advisor for
> getting some useful recommendation on your query commands (using that
> workload file)?
> From your message I understand that you are not with your current querying
> solution but I just wanted to stress that it would be much much better
> using SPs for quering against your database from your VB apps. Using SPs,
> you'll not be able to change your hard-coded SPs for minor code changings
> (which does not affect datasets that return to your app) and it would be
> more secure and fast (SPs are much faster and better than ad hocs)
> --
> Ekrem Önsoy
>
> "Elrond Bishop" <billgates@.microsoft.com> wrote in message
> news:ORurkmpBIHA.3900@.TK2MSFTNGP02.phx.gbl...
>> The database has a lot of "Dynamic" SQL coming into the database from VB
>> strings executed through the conneciton object.. Bad programming practice
>> I know but very little we can do about it at the moment.
>> Should I be worried about a higher CPU usage in that case ?
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23ZmiGEpBIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Well since 2005 can compile at the statement level vs. the entire batch
>> or proc there can be considerable savings in recompile costs. It they
>> are all compiles that means none of the plans are being reused. In that
>> case you may be able to get some help with the Force Parameterization
>> option or the use of plan guides. See here for more details:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Elrond Bishop" <billgates@.microsoft.com> wrote in message
>> news:ODJKGvoBIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi All
>> I read somewhere that the compiles / sec performance counter should be
>> used to evaluate the risk involved in upgrating a SQL 2000 Server to a
>> SQL 2005 server. That SQL 2005 is more CPU intensive on these tasks?
>> Does anyone have more documentation / information on the subject ?
>> Thanks
>> Elrond Bishop
>>
>|||SQL 2005 will not increase the compile rates if they are already not reusing
the plans in 2000. It may in fact decrease it especially if you utilize some
of the features I mentioned earlier. But having said that the engine is
always evolving and the only way to know for sure is to test it under your
real life conditions. But the solution is really to fix the way the app
makes the calls so that you either use sps or format the calls so that they
can be reused via autoparameterizaion or sp_executesql.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Elrond Bishop" <billgates@.microsoft.com> wrote in message
news:ORurkmpBIHA.3900@.TK2MSFTNGP02.phx.gbl...
> The database has a lot of "Dynamic" SQL coming into the database from VB
> strings executed through the conneciton object.. Bad programming practice
> I know but very little we can do about it at the moment.
> Should I be worried about a higher CPU usage in that case ?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23ZmiGEpBIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Well since 2005 can compile at the statement level vs. the entire batch
>> or proc there can be considerable savings in recompile costs. It they are
>> all compiles that means none of the plans are being reused. In that case
>> you may be able to get some help with the Force Parameterization option
>> or the use of plan guides. See here for more details:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Elrond Bishop" <billgates@.microsoft.com> wrote in message
>> news:ODJKGvoBIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi All
>> I read somewhere that the compiles / sec performance counter should be
>> used to evaluate the risk involved in upgrating a SQL 2000 Server to a
>> SQL 2005 server. That SQL 2005 is more CPU intensive on these tasks?
>> Does anyone have more documentation / information on the subject ?
>> Thanks
>> Elrond Bishop
>|||With a bunch of dynamic sql it is quite possible you could very much benefit
from FORCED PARAMETERIZATION setting in 2005, as Andrew mentioned. Dynamic
sql that does lots of statement calls where it simply changes the values in
one or more WHERE clause lines is a great example of code that would
benefit. The first execution would be parameterized and the (hopefully
index seek/bookmark lookup) query plan cached. then as the code looped
through the remaining where clause values (such as EmpID = 1, EmpID = 2 ...)
the cached query plan will be reused - saving a lot of effort on compiles.
I have seen this very thing result in 30-40% throughput inprovement at one
of my clients.
"Elrond Bishop" <billgates@.microsoft.com> wrote in message
news:ORurkmpBIHA.3900@.TK2MSFTNGP02.phx.gbl...
> The database has a lot of "Dynamic" SQL coming into the database from VB
> strings executed through the conneciton object.. Bad programming practice
> I know but very little we can do about it at the moment.
> Should I be worried about a higher CPU usage in that case ?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23ZmiGEpBIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Well since 2005 can compile at the statement level vs. the entire batch
>> or proc there can be considerable savings in recompile costs. It they are
>> all compiles that means none of the plans are being reused. In that case
>> you may be able to get some help with the Force Parameterization option
>> or the use of plan guides. See here for more details:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Elrond Bishop" <billgates@.microsoft.com> wrote in message
>> news:ODJKGvoBIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi All
>> I read somewhere that the compiles / sec performance counter should be
>> used to evaluate the risk involved in upgrating a SQL 2000 Server to a
>> SQL 2005 server. That SQL 2005 is more CPU intensive on these tasks?
>> Does anyone have more documentation / information on the subject ?
>> Thanks
>> Elrond Bishop
>
Subscribe to:
Posts (Atom)