Hi, All
Recently, I was running a script like:
declare @.sql nvarchar(4000)
declare @.db sysname ; set @.db = DB_NAME()
declare @.u sysname ; set @.u = QUOTENAME('db_executor')
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'') = 0'
exec master.dbo.xp_execresultset @.sql,@.db
to grant stored procedures execution permition to role, after running the
script, the privilige is grant it to that role, but I got very strange
things happened on sql server.
1. Master database get Information_Shema as a user. dbaccess "via group
membership"
2.Master database got system_function_schema as a user , dbaccess "via group
membership"
3.guest user show in every database in the instance, dbaccess "via group
membership"
if I delete guest account, show me message "the user is not in
database", sp_helpuser did not show guest user, enterprice manager show it
as a user.
4. run sp_grantdbaccess guest. the guest get permitted to use database. I
delete it successful, however, a couple minutes later, it appears in the db
as dbaccess via group membership.
it is really painful for me. could anyone give me a hint to fix the problem?
any help is appreciated.
Thanks
YifeiHi, All,
I think this is an issue for sql 2005, because the when I open em from db
server, the information_schema user does not show, but I connect it through
a remote machine that installed sql 2005, and the user showed.
same as another machine that did not run the script and did installed sql
2005.
Yifei
"Yifei" <yjiang@.sdg.aust.com> wrote in message
news:uVLA%23HI$FHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi, All
> Recently, I was running a script like:
> declare @.sql nvarchar(4000)
> declare @.db sysname ; set @.db = DB_NAME()
> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
> INFORMATION_SCHEMA.ROUTINES ' +
> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'') = 0'
> exec master.dbo.xp_execresultset @.sql,@.db
> to grant stored procedures execution permition to role, after running the
> script, the privilige is grant it to that role, but I got very strange
> things happened on sql server.
> 1. Master database get Information_Shema as a user. dbaccess "via group
> membership"
> 2.Master database got system_function_schema as a user , dbaccess "via
> group
> membership"
> 3.guest user show in every database in the instance, dbaccess "via group
> membership"
> if I delete guest account, show me message "the user is not in
> database", sp_helpuser did not show guest user, enterprice manager show it
> as a user.
> 4. run sp_grantdbaccess guest. the guest get permitted to use database. I
> delete it successful, however, a couple minutes later, it appears in the
> db
> as dbaccess via group membership.
> it is really painful for me. could anyone give me a hint to fix the
> problem?
> any help is appreciated.
> Thanks
> Yifei
>
>
Showing posts with label running. Show all posts
Showing posts with label running. Show all posts
Friday, March 30, 2012
Information_Schema disappears?
Recently we had a development server on WIN2K running SQL 2K. We noticed that the Information_Schema views disappeared. We have no idea how. It appeared to be about the time most recent patches were run. However, patches were applied to many servers, and the others all have Information_Schema
We reinstalled (needed an os upgrade) the OS bringing it up to Win2003, and reinstalled SQL2K. All patches were installed on both the operating system and sql
Information_Schema was present
We have not reinstalled
We have not rebooted
Information_Schema is now not present
Last week's queries against it now fail
Any ideas?It appears that the views are still in Master, but have owner dbo
They can only be accessed in Master, and don't have data for other db's
What would cause this
What can be done to correct
Thanks!|||Any ideas on this
It is truly vexing, and we'd appreciate some input here!
Thanks.|||I've never seen or heard about this. Assuming you have searched KB already,
this sounds like an MS Support case to me...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:E5C10653-0B4F-4136-AB59-51705B35FE2C@.microsoft.com...
> Any ideas on this?
> It is truly vexing, and we'd appreciate some input here!!
> Thanks.
We reinstalled (needed an os upgrade) the OS bringing it up to Win2003, and reinstalled SQL2K. All patches were installed on both the operating system and sql
Information_Schema was present
We have not reinstalled
We have not rebooted
Information_Schema is now not present
Last week's queries against it now fail
Any ideas?It appears that the views are still in Master, but have owner dbo
They can only be accessed in Master, and don't have data for other db's
What would cause this
What can be done to correct
Thanks!|||Any ideas on this
It is truly vexing, and we'd appreciate some input here!
Thanks.|||I've never seen or heard about this. Assuming you have searched KB already,
this sounds like an MS Support case to me...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:E5C10653-0B4F-4136-AB59-51705B35FE2C@.microsoft.com...
> Any ideas on this?
> It is truly vexing, and we'd appreciate some input here!!
> Thanks.
Information_Schema disappears?
Recently we had a development server on WIN2K running SQL 2K. We noticed t
hat the Information_Schema views disappeared. We have no idea how. It appea
red to be about the time most recent patches were run. However, patches were
applied to many servers, a
nd the others all have Information_Schema.
We reinstalled (needed an os upgrade) the OS bringing it up to Win2003, and
reinstalled SQL2K. All patches were installed on both the operating system
and sql.
Information_Schema was present.
We have not reinstalled.
We have not rebooted.
Information_Schema is now not present.
Last week's queries against it now fail.
Any ideas?It appears that the views are still in Master, but have owner dbo.
They can only be accessed in Master, and don't have data for other db's.
What would cause this?
What can be done to correct?
Thanks!|||Any ideas on this?
It is truly vexing, and we'd appreciate some input here!!
Thanks.|||I've never seen or heard about this. Assuming you have searched KB already,
this sounds like an MS Support case to me...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:E5C10653-0B4F-4136-AB59-51705B35FE2C@.microsoft.com...
> Any ideas on this?
> It is truly vexing, and we'd appreciate some input here!!
> Thanks.
hat the Information_Schema views disappeared. We have no idea how. It appea
red to be about the time most recent patches were run. However, patches were
applied to many servers, a
nd the others all have Information_Schema.
We reinstalled (needed an os upgrade) the OS bringing it up to Win2003, and
reinstalled SQL2K. All patches were installed on both the operating system
and sql.
Information_Schema was present.
We have not reinstalled.
We have not rebooted.
Information_Schema is now not present.
Last week's queries against it now fail.
Any ideas?It appears that the views are still in Master, but have owner dbo.
They can only be accessed in Master, and don't have data for other db's.
What would cause this?
What can be done to correct?
Thanks!|||Any ideas on this?
It is truly vexing, and we'd appreciate some input here!!
Thanks.|||I've never seen or heard about this. Assuming you have searched KB already,
this sounds like an MS Support case to me...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:E5C10653-0B4F-4136-AB59-51705B35FE2C@.microsoft.com...
> Any ideas on this?
> It is truly vexing, and we'd appreciate some input here!!
> Thanks.
Monday, March 26, 2012
Info on Load Testing Tools Needed
Hello, we are a MS SQL 2000 shop and use a web server running IIS to serve up
document images to Internet users (about 1,500 a day). Can someone recommend
me some quality simulator tools that we can use to simulate 1500 users
hitting our website daily, and 4500 query requests to our SQL Server daily?
Thanks in advance for any info.
Here is a huge list:
http://www.softwareqatest.com/qatweb1.html
You could also use, MS ACT:
http://msdn.microsoft.com/library/de...actml_main.asp
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:4CFE0B70-51C4-4A88-9AF9-A8CE0D09D02F@.microsoft.com...
> Hello, we are a MS SQL 2000 shop and use a web server running IIS to serve up
> document images to Internet users (about 1,500 a day). Can someone recommend
> me some quality simulator tools that we can use to simulate 1500 users
> hitting our website daily, and 4500 query requests to our SQL Server daily?
> Thanks in advance for any info.
|||Thanks--looks like good stuff!
"oj" wrote:
> Here is a huge list:
> http://www.softwareqatest.com/qatweb1.html
> You could also use, MS ACT:
> http://msdn.microsoft.com/library/de...actml_main.asp
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:4CFE0B70-51C4-4A88-9AF9-A8CE0D09D02F@.microsoft.com...
>
>
document images to Internet users (about 1,500 a day). Can someone recommend
me some quality simulator tools that we can use to simulate 1500 users
hitting our website daily, and 4500 query requests to our SQL Server daily?
Thanks in advance for any info.
Here is a huge list:
http://www.softwareqatest.com/qatweb1.html
You could also use, MS ACT:
http://msdn.microsoft.com/library/de...actml_main.asp
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:4CFE0B70-51C4-4A88-9AF9-A8CE0D09D02F@.microsoft.com...
> Hello, we are a MS SQL 2000 shop and use a web server running IIS to serve up
> document images to Internet users (about 1,500 a day). Can someone recommend
> me some quality simulator tools that we can use to simulate 1500 users
> hitting our website daily, and 4500 query requests to our SQL Server daily?
> Thanks in advance for any info.
|||Thanks--looks like good stuff!
"oj" wrote:
> Here is a huge list:
> http://www.softwareqatest.com/qatweb1.html
> You could also use, MS ACT:
> http://msdn.microsoft.com/library/de...actml_main.asp
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:4CFE0B70-51C4-4A88-9AF9-A8CE0D09D02F@.microsoft.com...
>
>
Info on Load Testing Tools Needed
Hello, we are a MS SQL 2000 shop and use a web server running IIS to serve up
document images to Internet users (about 1,500 a day). Can someone recommend
me some quality simulator tools that we can use to simulate 1500 users
hitting our website daily, and 4500 query requests to our SQL Server daily?
Thanks in advance for any info.Here is a huge list:
http://www.softwareqatest.com/qatweb1.html
You could also use, MS ACT:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/act/htm/actml_main.asp
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:4CFE0B70-51C4-4A88-9AF9-A8CE0D09D02F@.microsoft.com...
> Hello, we are a MS SQL 2000 shop and use a web server running IIS to serve up
> document images to Internet users (about 1,500 a day). Can someone recommend
> me some quality simulator tools that we can use to simulate 1500 users
> hitting our website daily, and 4500 query requests to our SQL Server daily?
> Thanks in advance for any info.
document images to Internet users (about 1,500 a day). Can someone recommend
me some quality simulator tools that we can use to simulate 1500 users
hitting our website daily, and 4500 query requests to our SQL Server daily?
Thanks in advance for any info.Here is a huge list:
http://www.softwareqatest.com/qatweb1.html
You could also use, MS ACT:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/act/htm/actml_main.asp
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:4CFE0B70-51C4-4A88-9AF9-A8CE0D09D02F@.microsoft.com...
> Hello, we are a MS SQL 2000 shop and use a web server running IIS to serve up
> document images to Internet users (about 1,500 a day). Can someone recommend
> me some quality simulator tools that we can use to simulate 1500 users
> hitting our website daily, and 4500 query requests to our SQL Server daily?
> Thanks in advance for any info.
info about sysprocesses
Dear all,
I want to know all the possible values for the status field bring up for
sysprocesses table. Values such 'running' or 'sleeping' seems very
evident but there is one so-called 'DEF-WK...' or something like that which
I haven't idea.
In this occasion I am not be able to find it inside the BOL
Does anyone know how do I figure out such values?
Thanks in advance,
EnricHi
Look at the code from the system SP sp_who2
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:C9B83081-19A4-4A33-AD4C-F75AE450B394@.microsoft.com...
> Dear all,
> I want to know all the possible values for the status field bring up for
> sysprocesses table. Values such 'running' or 'sleeping' seems very
> evident but there is one so-called 'DEF-WK...' or something like that
> which
> I haven't idea.
> In this occasion I am not be able to find it inside the BOL
> Does anyone know how do I figure out such values?
> Thanks in advance,
> Enric
I want to know all the possible values for the status field bring up for
sysprocesses table. Values such 'running' or 'sleeping' seems very
evident but there is one so-called 'DEF-WK...' or something like that which
I haven't idea.
In this occasion I am not be able to find it inside the BOL
Does anyone know how do I figure out such values?
Thanks in advance,
EnricHi
Look at the code from the system SP sp_who2
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:C9B83081-19A4-4A33-AD4C-F75AE450B394@.microsoft.com...
> Dear all,
> I want to know all the possible values for the status field bring up for
> sysprocesses table. Values such 'running' or 'sleeping' seems very
> evident but there is one so-called 'DEF-WK...' or something like that
> which
> I haven't idea.
> In this occasion I am not be able to find it inside the BOL
> Does anyone know how do I figure out such values?
> Thanks in advance,
> Enric
Info about a running job step
Hi group,
I need to retrieve information about a running step, specifically its
status. When a step is being executed, the EM always knows and shows so
(under status showing Executing Job Step 'n (step name)'). However, I can
not find such information in the system tables. where does EM get the info
from? How can I access this info with a query, or better yet, within a
stored proc? Help is appreciated.
QuentinYou can use sp_help_job e.g.
exec msdb..sp_help_job
@.job_name = 'jobname ,
@.job_aspect = 'JOB'
Look at the current_execution_status and current_execution_step columns
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Quentin Ran" <ab@.who.com> wrote in message
news:OxXqrA3VDHA.612@.TK2MSFTNGP10.phx.gbl...
Hi group,
I need to retrieve information about a running step, specifically its
status. When a step is being executed, the EM always knows and shows so
(under status showing Executing Job Step 'n (step name)'). However, I can
not find such information in the system tables. where does EM get the info
from? How can I access this info with a query, or better yet, within a
stored proc? Help is appreciated.
Quentin|||Thanks Mr. Dentist.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:efTLfj4VDHA.1816@.TK2MSFTNGP09.phx.gbl...
> You can use sp_help_job e.g.
> exec msdb..sp_help_job
> @.job_name = 'jobname ,
> @.job_aspect = 'JOB'
> Look at the current_execution_status and current_execution_step columns
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Quentin Ran" <ab@.who.com> wrote in message
> news:OxXqrA3VDHA.612@.TK2MSFTNGP10.phx.gbl...
> Hi group,
> I need to retrieve information about a running step, specifically its
> status. When a step is being executed, the EM always knows and shows so
> (under status showing Executing Job Step 'n (step name)'). However, I can
> not find such information in the system tables. where does EM get the
info
> from? How can I access this info with a query, or better yet, within a
> stored proc? Help is appreciated.
> Quentin
>
I need to retrieve information about a running step, specifically its
status. When a step is being executed, the EM always knows and shows so
(under status showing Executing Job Step 'n (step name)'). However, I can
not find such information in the system tables. where does EM get the info
from? How can I access this info with a query, or better yet, within a
stored proc? Help is appreciated.
QuentinYou can use sp_help_job e.g.
exec msdb..sp_help_job
@.job_name = 'jobname ,
@.job_aspect = 'JOB'
Look at the current_execution_status and current_execution_step columns
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Quentin Ran" <ab@.who.com> wrote in message
news:OxXqrA3VDHA.612@.TK2MSFTNGP10.phx.gbl...
Hi group,
I need to retrieve information about a running step, specifically its
status. When a step is being executed, the EM always knows and shows so
(under status showing Executing Job Step 'n (step name)'). However, I can
not find such information in the system tables. where does EM get the info
from? How can I access this info with a query, or better yet, within a
stored proc? Help is appreciated.
Quentin|||Thanks Mr. Dentist.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:efTLfj4VDHA.1816@.TK2MSFTNGP09.phx.gbl...
> You can use sp_help_job e.g.
> exec msdb..sp_help_job
> @.job_name = 'jobname ,
> @.job_aspect = 'JOB'
> Look at the current_execution_status and current_execution_step columns
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Quentin Ran" <ab@.who.com> wrote in message
> news:OxXqrA3VDHA.612@.TK2MSFTNGP10.phx.gbl...
> Hi group,
> I need to retrieve information about a running step, specifically its
> status. When a step is being executed, the EM always knows and shows so
> (under status showing Executing Job Step 'n (step name)'). However, I can
> not find such information in the system tables. where does EM get the
info
> from? How can I access this info with a query, or better yet, within a
> stored proc? Help is appreciated.
> Quentin
>
Friday, March 23, 2012
Infameous 0x80070005 ERROR: Access denied
Hi, my name is Beatriz and I'm another victim of the 0x80070005 from
Hell. I'm running WBEMTEST from a PC to connect to another one with WXP
SP2 and even:
missing?!?!?!?Are you looking for SQL Server Integration Services help?|||
Hell. I'm running WBEMTEST from a PC to connect to another one with WXP
SP2 and even:
1. The firewall is down.
2. Applied the commands:
netsh firewall set service RemoteAdmin enable
netsh firewall add portopening protocol=tcp port=135 name=DCOM_TCP135
3. Did the workaround suggested in:
http://support.microsoft.com/kb/909444
4. Ran the workaround in: http://support.microsoft.com/kb/884573
5. Added the account I use to access by Wbemtest with to the Default
Lauch Permissions with Full Control.
6. Checked up the registry
:HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\forceguest
were set to 0.
missing?!?!?!?Are you looking for SQL Server Integration Services help?|||
Did you ever get a response or resolution to this?
Hopefully, your system isn't still DOA 7-8 months later.
We have exactly the same issue with a brand-new server running WinServer 2003x64 R2
Have installed/reinstalled the entire OS three times now. The solution I guess is to do yet another reinstall and then not install the rogue security patch but that would leave us vulnerable to whatever the security patch was trying to fix and who knows what future M/Soft update won't do something similar?
|||Are you having SSIS problems when trying to connect to the SSIS service via Management Studio?http://www.ssistalk.com/2007/04/13/ssis-access-is-denied-when-connection-to-remote-ssis-service/
Infameous 0x80070005 ERROR: Access denied
Hi, my name is Beatriz and I'm another victim of the 0x80070005 from
Hell. I'm running WBEMTEST from a PC to connect to another one with WXP
SP2 and even:
missing?!?!?!?Are you looking for SQL Server Integration Services help?|||
Hell. I'm running WBEMTEST from a PC to connect to another one with WXP
SP2 and even:
1. The firewall is down.
2. Applied the commands:
netsh firewall set service RemoteAdmin enable
netsh firewall add portopening protocol=tcp port=135 name=DCOM_TCP135
3. Did the workaround suggested in:
http://support.microsoft.com/kb/909444
4. Ran the workaround in: http://support.microsoft.com/kb/884573
5. Added the account I use to access by Wbemtest with to the Default
Lauch Permissions with Full Control.
6. Checked up the registry
:HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\forceguest
were set to 0.
missing?!?!?!?Are you looking for SQL Server Integration Services help?|||
Did you ever get a response or resolution to this?
Hopefully, your system isn't still DOA 7-8 months later.
We have exactly the same issue with a brand-new server running WinServer 2003x64 R2
Have installed/reinstalled the entire OS three times now. The solution I guess is to do yet another reinstall and then not install the rogue security patch but that would leave us vulnerable to whatever the security patch was trying to fix and who knows what future M/Soft update won't do something similar?
|||Are you having SSIS problems when trying to connect to the SSIS service via Management Studio?http://www.ssistalk.com/2007/04/13/ssis-access-is-denied-when-connection-to-remote-ssis-service/
Ineeficient Memory Usage
I have an instance of SQL Server 2000 (SP4) running on Win 2K.
On it I have a task that runs to decompose xml into tables using
sp_prepare/sp_remove xml doc procedures and open xml.
When the server is booted or sql server and sql server agent process
restarted it sits happily using about 60MB of memory. However once I start my
xml decomposition task the memory gradually creeps up until it hits the
maximum.
Now I know SQL server will effectively grab all the memory it can and keep it.
However my issue is not with the amount of memory it has taken, but the way
it would appear to use it.
This is because when the task first kicks off it runs like grease lightening
and will decompose about 1000 1MB XML files a minute. However once the memory
has reached maximim this is down to 1 1MB XML file a minute. Quite a
significant difference despite the file being indetical in structure (they
are messages with different content).
I have tried to use profiler to determine where the problem is, but it looks
most likely that sql server is not releasing enough unused memory back to
itself. The overhead is in the passing of large chunks of data between stored
procedures and the use of the xml (sp_prepare and sp_remove) and this is only
a significant overhead once SQL Server has taken all the memory for itself.
Also once the task is stopped at no point is memory ever released by SQL
Serevr even if there is no activity at all for hours/days.
Is this a known issue/bug or is it possible that I have done something wrong?
Note sp_remove is always called after an sp_prepare so I assume that this
should unallocate any memory used by that.
Alternatively if a parent procedure runs continually calling the same child
procedure, do the variables local to the child procedure get cleared properly
between each call if the parent is still active?
I'm very perplexed and would greatly appreciate some assistance/ideas
Cheers
Tom
Have I posted this in the wrong place or is my wording too poor for anyone to
assist?
I had hoped to see some replies by now.
Please feel free to state the obvious as I may have missed something simple.
Cheers
Tom
"TomPearson" wrote:
> I have an instance of SQL Server 2000 (SP4) running on Win 2K.
> On it I have a task that runs to decompose xml into tables using
> sp_prepare/sp_remove xml doc procedures and open xml.
> When the server is booted or sql server and sql server agent process
> restarted it sits happily using about 60MB of memory. However once I start my
> xml decomposition task the memory gradually creeps up until it hits the
> maximum.
> Now I know SQL server will effectively grab all the memory it can and keep it.
> However my issue is not with the amount of memory it has taken, but the way
> it would appear to use it.
> This is because when the task first kicks off it runs like grease lightening
> and will decompose about 1000 1MB XML files a minute. However once the memory
> has reached maximim this is down to 1 1MB XML file a minute. Quite a
> significant difference despite the file being indetical in structure (they
> are messages with different content).
> I have tried to use profiler to determine where the problem is, but it looks
> most likely that sql server is not releasing enough unused memory back to
> itself. The overhead is in the passing of large chunks of data between stored
> procedures and the use of the xml (sp_prepare and sp_remove) and this is only
> a significant overhead once SQL Server has taken all the memory for itself.
> Also once the task is stopped at no point is memory ever released by SQL
> Serevr even if there is no activity at all for hours/days.
> Is this a known issue/bug or is it possible that I have done something wrong?
> Note sp_remove is always called after an sp_prepare so I assume that this
> should unallocate any memory used by that.
> Alternatively if a parent procedure runs continually calling the same child
> procedure, do the variables local to the child procedure get cleared properly
> between each call if the parent is still active?
> I'm very perplexed and would greatly appreciate some assistance/ideas
> Cheers
> Tom
>
|||Have I posted this in the wrong place?
I had hoped for some replies by now.
If my wording is poor or unclear I am will hapily clarify as I could really
do with some advice on this issue.
Also don't be afraid to state the obvious as I may have missed something
really simple.
Cheers
Tom
"TomPearson" wrote:
> I have an instance of SQL Server 2000 (SP4) running on Win 2K.
> On it I have a task that runs to decompose xml into tables using
> sp_prepare/sp_remove xml doc procedures and open xml.
> When the server is booted or sql server and sql server agent process
> restarted it sits happily using about 60MB of memory. However once I start my
> xml decomposition task the memory gradually creeps up until it hits the
> maximum.
> Now I know SQL server will effectively grab all the memory it can and keep it.
> However my issue is not with the amount of memory it has taken, but the way
> it would appear to use it.
> This is because when the task first kicks off it runs like grease lightening
> and will decompose about 1000 1MB XML files a minute. However once the memory
> has reached maximim this is down to 1 1MB XML file a minute. Quite a
> significant difference despite the file being indetical in structure (they
> are messages with different content).
> I have tried to use profiler to determine where the problem is, but it looks
> most likely that sql server is not releasing enough unused memory back to
> itself. The overhead is in the passing of large chunks of data between stored
> procedures and the use of the xml (sp_prepare and sp_remove) and this is only
> a significant overhead once SQL Server has taken all the memory for itself.
> Also once the task is stopped at no point is memory ever released by SQL
> Serevr even if there is no activity at all for hours/days.
> Is this a known issue/bug or is it possible that I have done something wrong?
> Note sp_remove is always called after an sp_prepare so I assume that this
> should unallocate any memory used by that.
> Alternatively if a parent procedure runs continually calling the same child
> procedure, do the variables local to the child procedure get cleared properly
> between each call if the parent is still active?
> I'm very perplexed and would greatly appreciate some assistance/ideas
> Cheers
> Tom
>
|||Hi Tom
This is the right newsgroup. But since it is a newsgroup, it may take a
while until your posting gets distributed and people answer.
There are lots of reasons why this may happen, including a problem with SQL
Server. Are there any other processes going on in the database that may use
up memory and resources (other queries, allocating temp tables etc)?
Can you send us a repro, so that we can investigate it in our lab to see
whether it repros and if, why?
Thanks
Michael
"TomPearson" <TomPearson@.discussions.microsoft.com> wrote in message
news:7F720240-6B2A-414F-8296-1025383DB57C@.microsoft.com...[vbcol=seagreen]
> Have I posted this in the wrong place?
> I had hoped for some replies by now.
> If my wording is poor or unclear I am will hapily clarify as I could
> really
> do with some advice on this issue.
> Also don't be afraid to state the obvious as I may have missed something
> really simple.
> Cheers
> Tom
> "TomPearson" wrote:
|||I have tried this in isolation without any other db queries, processes etc
going on and the result is the same. No temp tables are used during this task.
I would be eager to have someone look into the problem, but I don't
understand what you mean by a repro I'm afraid.
Cheers
Tom
"Michael Rys [MSFT]" wrote:
> Hi Tom
> This is the right newsgroup. But since it is a newsgroup, it may take a
> while until your posting gets distributed and people answer.
> There are lots of reasons why this may happen, including a problem with SQL
> Server. Are there any other processes going on in the database that may use
> up memory and resources (other queries, allocating temp tables etc)?
> Can you send us a repro, so that we can investigate it in our lab to see
> whether it repros and if, why?
> Thanks
> Michael
> "TomPearson" <TomPearson@.discussions.microsoft.com> wrote in message
> news:7F720240-6B2A-414F-8296-1025383DB57C@.microsoft.com...
>
>
|||A repro is a T-SQL script, data and (if necessary) some code that shows the
behaviour so we can try to run it in our environment to see whether we can
find the reason for the behaviour (as per email exchange).
Best regards
Michael
"TomPearson" <TomPearson@.discussions.microsoft.com> wrote in message
news:567A03AE-AEF4-4147-A39E-6D2F9EADB4E3@.microsoft.com...[vbcol=seagreen]
>I have tried this in isolation without any other db queries, processes etc
> going on and the result is the same. No temp tables are used during this
> task.
> I would be eager to have someone look into the problem, but I don't
> understand what you mean by a repro I'm afraid.
> Cheers
> Tom
>
> "Michael Rys [MSFT]" wrote:
|||Michael,
I have similler issue with the MSXML parser with Visual Studio. Here I have
a sample code. Can you run this and see you find any thing worng with my code.
thanks in advance.
#include "stdafx.h"
#include <msxml2.h>
#include "comutil.h"
#include "psapi.h"
void Init();
void Release();
BOOL LoadXML(LPSTR lpzxml);
BOOL Go_To_Parent(LPCSTR lpszNodeName);
BOOL GetChild(LPCSTR lpszNodeName, LPCSTR lpszNodeValue);
void PrintMemoryInfo( DWORD processID, int Iteration );
IXMLDOMDocument2*pXMLDom;
IXMLDOMNode*pNode;
int _tmain(int argc, _TCHAR* argv[])
{
LPSTR lpzxml =
"<PromptDataRoot><Errors>OK</Errors><NodeId>3</NodeId><Action></Action><SessionDataRoot><SessionId>001050625171432 00002</SessionId><DateTime>6/25/2005
5:14:37
PM</DateTime><CurrentDataItem>TNI</CurrentDataItem><LookupUserInputKey>1</LookupUserInputKey><DataCollected><InitialUserKey> 1</InitialUserKey><LANGUAGECHOICE>1</LANGUAGECHOICE></DataCollected><StepLog><PromptData><NodeId>2</NodeId><UserInputKey>1</UserInp
utKey><LookupKey>1</LookupKey></PromptData><Action></Action></StepLog></SessionDataRoot><PromptControlData><Language>0</Language><MainMessage><MsgId>!102</MsgId><ReplayMsgKey></ReplayMsgKey><CorrectValues></CorrectValues><CharSet>0123456789</CharSet><Leng
th>10</Length><Timeout>10000</Timeout><TimeOutMsgId>!102</TimeOutMsgId><RetryCount>3</RetryCount><RetryExitAction><Transfer></Transfer><End>N</End></RetryExitAction><RetryExitMsgId>!125</RetryExitMsgId></MainMessage><ConfirmMessage><MsgId>!103
^# !104</MsgId><CorrectValues>1,2
</CorrectValues><CharSet></CharSet><Length>1</Length><Accept>1</Accept><Reject>2</Reject><Timeout>10000</Timeout><TimeOutMsgId>!103
^#
!104</TimeOutMsgId><RetryCount>2</RetryCount><RetryExitAction><Transfer><Record>N</Record><Phone>2003207</Phone><BusyAction>End</BusyAction><BusyMsgId>!123</BusyMsgId></Transfer><End>N</End><CompletionStatus>1</CompletionStatus></RetryExitAction><RetryExi
tMsgId>!126</RetryExitMsgId></ConfirmMessage></PromptControlData><SpecialProcessing><MiscMessageI dTags></MiscMessageIdTags></SpecialProcessing></PromptDataRoot>";
char szValue[512];
DWORD x;
for(int i = 0 ;i<1000;i++)
{
Init();
LoadXML(lpzxml);
Go_To_Parent("PromptDataRoot");
GetChild("Errors", szValue);
Release();
x = GetCurrentProcessId();
PrintMemoryInfo(x, i);
Sleep(10000);
}
MessageBox(NULL, "Done", "Info", MB_OK);
return 0;
}
void Init()
{
CoInitialize(NULL);
CoCreateInstance(__uuidof(DOMDocument40),
NULL,
CLSCTX_INPROC_SERVER,
__uuidof(IXMLDOMDocument2),
(void**)&pXMLDom);
pXMLDom->put_async(VARIANT_FALSE);
pXMLDom->put_validateOnParse(VARIANT_FALSE);
pXMLDom->put_resolveExternals(VARIANT_FALSE);
}
void Release()
{
if (pNode)
pNode->Release();
if (pXMLDom)
pXMLDom->Release();
CoUninitialize();
}
BOOL LoadXML(LPSTR lpzxml)
{
VARIANT_BOOL status;
BSTR bstrXML;
try{
bstrXML = _bstr_t(lpzxml);
pXMLDom->loadXML(bstrXML, &status);
return TRUE;
}
catch(...)
{
return FALSE;
}
}
BOOL Go_To_Parent(LPCSTR lpszNodeName)
{
IXMLDOMElement *pElementRoot;
IXMLDOMNodeList *pNodeList;
BSTRbstrValue;
long lNodeLength;
try{
pXMLDom->get_documentElement(&pElementRoot);
pElementRoot->getElementsByTagName(_bstr_t(lpszNodeName), &pNodeList);
pNodeList->get_length(&lNodeLength);
if (lNodeLength > 0)
{
pNodeList->get_item(0, &pNode);
return TRUE;
}
else
{
pXMLDom->get_childNodes(&pNodeList);
pNodeList->get_item(0, &pNode);
pNode->get_xml(&bstrValue);
return TRUE;
}
}
catch(...)
{
return FALSE;
}
}
BOOL GetChild(LPCSTR lpszNodeName, LPCSTR lpszNodeValue)
{
BSTR bstrValue;
long lLength, l;
IXMLDOMNodeList *pNodeList;
IXMLDOMNode *pLocalNode;
try{
pNode->get_childNodes(&pNodeList);
pNodeList->get_length(&lLength);
for (l=0;l <=lLength;l++)
{
pNodeList->get_item(l, &pLocalNode);
pLocalNode->get_xml(&bstrValue);
pLocalNode->get_nodeName(&bstrValue);
if (strcmp((LPSTR)_bstr_t(bstrValue), lpszNodeName) == 0)
{
pLocalNode->get_text(&bstrValue);
sprintf((char *)lpszNodeValue, "%s", (LPCSTR)_bstr_t(bstrValue));
return TRUE;
pNodeList->Release();
pLocalNode->Release();
}
}
return FALSE;
}
catch(...)
{
return FALSE;
}
}
void PrintMemoryInfo( DWORD processID, int Iteration )
{
HANDLE hProcess;
PROCESS_MEMORY_COUNTERS pmc;
TCHAR szProcessName[MAX_PATH] = TEXT("<unknown>");
hProcess = OpenProcess( PROCESS_QUERY_INFORMATION |
PROCESS_VM_READ,
FALSE, processID );
if (NULL == hProcess)
return;
HMODULE hMod;
DWORD cbNeeded;
if ( EnumProcessModules( hProcess, &hMod, sizeof(hMod),
&cbNeeded) )
{
GetModuleBaseName( hProcess, hMod, szProcessName,
sizeof(szProcessName)/sizeof(TCHAR) );
}
if ( GetProcessMemoryInfo( hProcess, &pmc, sizeof(pmc)) )
printf( "Iteration :%d, ProcessName :%s, PID:%u, Memory Usage:%dk
\n",Iteration, szProcessName, processID ,pmc.WorkingSetSize/1024);
CloseHandle( hProcess );
}
"Michael Rys [MSFT]" wrote:
> A repro is a T-SQL script, data and (if necessary) some code that shows the
> behaviour so we can try to run it in our environment to see whether we can
> find the reason for the behaviour (as per email exchange).
> Best regards
> Michael
> "TomPearson" <TomPearson@.discussions.microsoft.com> wrote in message
> news:567A03AE-AEF4-4147-A39E-6D2F9EADB4E3@.microsoft.com...
>
>
On it I have a task that runs to decompose xml into tables using
sp_prepare/sp_remove xml doc procedures and open xml.
When the server is booted or sql server and sql server agent process
restarted it sits happily using about 60MB of memory. However once I start my
xml decomposition task the memory gradually creeps up until it hits the
maximum.
Now I know SQL server will effectively grab all the memory it can and keep it.
However my issue is not with the amount of memory it has taken, but the way
it would appear to use it.
This is because when the task first kicks off it runs like grease lightening
and will decompose about 1000 1MB XML files a minute. However once the memory
has reached maximim this is down to 1 1MB XML file a minute. Quite a
significant difference despite the file being indetical in structure (they
are messages with different content).
I have tried to use profiler to determine where the problem is, but it looks
most likely that sql server is not releasing enough unused memory back to
itself. The overhead is in the passing of large chunks of data between stored
procedures and the use of the xml (sp_prepare and sp_remove) and this is only
a significant overhead once SQL Server has taken all the memory for itself.
Also once the task is stopped at no point is memory ever released by SQL
Serevr even if there is no activity at all for hours/days.
Is this a known issue/bug or is it possible that I have done something wrong?
Note sp_remove is always called after an sp_prepare so I assume that this
should unallocate any memory used by that.
Alternatively if a parent procedure runs continually calling the same child
procedure, do the variables local to the child procedure get cleared properly
between each call if the parent is still active?
I'm very perplexed and would greatly appreciate some assistance/ideas
Cheers
Tom
Have I posted this in the wrong place or is my wording too poor for anyone to
assist?
I had hoped to see some replies by now.
Please feel free to state the obvious as I may have missed something simple.
Cheers
Tom
"TomPearson" wrote:
> I have an instance of SQL Server 2000 (SP4) running on Win 2K.
> On it I have a task that runs to decompose xml into tables using
> sp_prepare/sp_remove xml doc procedures and open xml.
> When the server is booted or sql server and sql server agent process
> restarted it sits happily using about 60MB of memory. However once I start my
> xml decomposition task the memory gradually creeps up until it hits the
> maximum.
> Now I know SQL server will effectively grab all the memory it can and keep it.
> However my issue is not with the amount of memory it has taken, but the way
> it would appear to use it.
> This is because when the task first kicks off it runs like grease lightening
> and will decompose about 1000 1MB XML files a minute. However once the memory
> has reached maximim this is down to 1 1MB XML file a minute. Quite a
> significant difference despite the file being indetical in structure (they
> are messages with different content).
> I have tried to use profiler to determine where the problem is, but it looks
> most likely that sql server is not releasing enough unused memory back to
> itself. The overhead is in the passing of large chunks of data between stored
> procedures and the use of the xml (sp_prepare and sp_remove) and this is only
> a significant overhead once SQL Server has taken all the memory for itself.
> Also once the task is stopped at no point is memory ever released by SQL
> Serevr even if there is no activity at all for hours/days.
> Is this a known issue/bug or is it possible that I have done something wrong?
> Note sp_remove is always called after an sp_prepare so I assume that this
> should unallocate any memory used by that.
> Alternatively if a parent procedure runs continually calling the same child
> procedure, do the variables local to the child procedure get cleared properly
> between each call if the parent is still active?
> I'm very perplexed and would greatly appreciate some assistance/ideas
> Cheers
> Tom
>
|||Have I posted this in the wrong place?
I had hoped for some replies by now.
If my wording is poor or unclear I am will hapily clarify as I could really
do with some advice on this issue.
Also don't be afraid to state the obvious as I may have missed something
really simple.
Cheers
Tom
"TomPearson" wrote:
> I have an instance of SQL Server 2000 (SP4) running on Win 2K.
> On it I have a task that runs to decompose xml into tables using
> sp_prepare/sp_remove xml doc procedures and open xml.
> When the server is booted or sql server and sql server agent process
> restarted it sits happily using about 60MB of memory. However once I start my
> xml decomposition task the memory gradually creeps up until it hits the
> maximum.
> Now I know SQL server will effectively grab all the memory it can and keep it.
> However my issue is not with the amount of memory it has taken, but the way
> it would appear to use it.
> This is because when the task first kicks off it runs like grease lightening
> and will decompose about 1000 1MB XML files a minute. However once the memory
> has reached maximim this is down to 1 1MB XML file a minute. Quite a
> significant difference despite the file being indetical in structure (they
> are messages with different content).
> I have tried to use profiler to determine where the problem is, but it looks
> most likely that sql server is not releasing enough unused memory back to
> itself. The overhead is in the passing of large chunks of data between stored
> procedures and the use of the xml (sp_prepare and sp_remove) and this is only
> a significant overhead once SQL Server has taken all the memory for itself.
> Also once the task is stopped at no point is memory ever released by SQL
> Serevr even if there is no activity at all for hours/days.
> Is this a known issue/bug or is it possible that I have done something wrong?
> Note sp_remove is always called after an sp_prepare so I assume that this
> should unallocate any memory used by that.
> Alternatively if a parent procedure runs continually calling the same child
> procedure, do the variables local to the child procedure get cleared properly
> between each call if the parent is still active?
> I'm very perplexed and would greatly appreciate some assistance/ideas
> Cheers
> Tom
>
|||Hi Tom
This is the right newsgroup. But since it is a newsgroup, it may take a
while until your posting gets distributed and people answer.
There are lots of reasons why this may happen, including a problem with SQL
Server. Are there any other processes going on in the database that may use
up memory and resources (other queries, allocating temp tables etc)?
Can you send us a repro, so that we can investigate it in our lab to see
whether it repros and if, why?
Thanks
Michael
"TomPearson" <TomPearson@.discussions.microsoft.com> wrote in message
news:7F720240-6B2A-414F-8296-1025383DB57C@.microsoft.com...[vbcol=seagreen]
> Have I posted this in the wrong place?
> I had hoped for some replies by now.
> If my wording is poor or unclear I am will hapily clarify as I could
> really
> do with some advice on this issue.
> Also don't be afraid to state the obvious as I may have missed something
> really simple.
> Cheers
> Tom
> "TomPearson" wrote:
|||I have tried this in isolation without any other db queries, processes etc
going on and the result is the same. No temp tables are used during this task.
I would be eager to have someone look into the problem, but I don't
understand what you mean by a repro I'm afraid.
Cheers
Tom
"Michael Rys [MSFT]" wrote:
> Hi Tom
> This is the right newsgroup. But since it is a newsgroup, it may take a
> while until your posting gets distributed and people answer.
> There are lots of reasons why this may happen, including a problem with SQL
> Server. Are there any other processes going on in the database that may use
> up memory and resources (other queries, allocating temp tables etc)?
> Can you send us a repro, so that we can investigate it in our lab to see
> whether it repros and if, why?
> Thanks
> Michael
> "TomPearson" <TomPearson@.discussions.microsoft.com> wrote in message
> news:7F720240-6B2A-414F-8296-1025383DB57C@.microsoft.com...
>
>
|||A repro is a T-SQL script, data and (if necessary) some code that shows the
behaviour so we can try to run it in our environment to see whether we can
find the reason for the behaviour (as per email exchange).
Best regards
Michael
"TomPearson" <TomPearson@.discussions.microsoft.com> wrote in message
news:567A03AE-AEF4-4147-A39E-6D2F9EADB4E3@.microsoft.com...[vbcol=seagreen]
>I have tried this in isolation without any other db queries, processes etc
> going on and the result is the same. No temp tables are used during this
> task.
> I would be eager to have someone look into the problem, but I don't
> understand what you mean by a repro I'm afraid.
> Cheers
> Tom
>
> "Michael Rys [MSFT]" wrote:
|||Michael,
I have similler issue with the MSXML parser with Visual Studio. Here I have
a sample code. Can you run this and see you find any thing worng with my code.
thanks in advance.
#include "stdafx.h"
#include <msxml2.h>
#include "comutil.h"
#include "psapi.h"
void Init();
void Release();
BOOL LoadXML(LPSTR lpzxml);
BOOL Go_To_Parent(LPCSTR lpszNodeName);
BOOL GetChild(LPCSTR lpszNodeName, LPCSTR lpszNodeValue);
void PrintMemoryInfo( DWORD processID, int Iteration );
IXMLDOMDocument2*pXMLDom;
IXMLDOMNode*pNode;
int _tmain(int argc, _TCHAR* argv[])
{
LPSTR lpzxml =
"<PromptDataRoot><Errors>OK</Errors><NodeId>3</NodeId><Action></Action><SessionDataRoot><SessionId>001050625171432 00002</SessionId><DateTime>6/25/2005
5:14:37
PM</DateTime><CurrentDataItem>TNI</CurrentDataItem><LookupUserInputKey>1</LookupUserInputKey><DataCollected><InitialUserKey> 1</InitialUserKey><LANGUAGECHOICE>1</LANGUAGECHOICE></DataCollected><StepLog><PromptData><NodeId>2</NodeId><UserInputKey>1</UserInp
utKey><LookupKey>1</LookupKey></PromptData><Action></Action></StepLog></SessionDataRoot><PromptControlData><Language>0</Language><MainMessage><MsgId>!102</MsgId><ReplayMsgKey></ReplayMsgKey><CorrectValues></CorrectValues><CharSet>0123456789</CharSet><Leng
th>10</Length><Timeout>10000</Timeout><TimeOutMsgId>!102</TimeOutMsgId><RetryCount>3</RetryCount><RetryExitAction><Transfer></Transfer><End>N</End></RetryExitAction><RetryExitMsgId>!125</RetryExitMsgId></MainMessage><ConfirmMessage><MsgId>!103
^# !104</MsgId><CorrectValues>1,2
</CorrectValues><CharSet></CharSet><Length>1</Length><Accept>1</Accept><Reject>2</Reject><Timeout>10000</Timeout><TimeOutMsgId>!103
^#
!104</TimeOutMsgId><RetryCount>2</RetryCount><RetryExitAction><Transfer><Record>N</Record><Phone>2003207</Phone><BusyAction>End</BusyAction><BusyMsgId>!123</BusyMsgId></Transfer><End>N</End><CompletionStatus>1</CompletionStatus></RetryExitAction><RetryExi
tMsgId>!126</RetryExitMsgId></ConfirmMessage></PromptControlData><SpecialProcessing><MiscMessageI dTags></MiscMessageIdTags></SpecialProcessing></PromptDataRoot>";
char szValue[512];
DWORD x;
for(int i = 0 ;i<1000;i++)
{
Init();
LoadXML(lpzxml);
Go_To_Parent("PromptDataRoot");
GetChild("Errors", szValue);
Release();
x = GetCurrentProcessId();
PrintMemoryInfo(x, i);
Sleep(10000);
}
MessageBox(NULL, "Done", "Info", MB_OK);
return 0;
}
void Init()
{
CoInitialize(NULL);
CoCreateInstance(__uuidof(DOMDocument40),
NULL,
CLSCTX_INPROC_SERVER,
__uuidof(IXMLDOMDocument2),
(void**)&pXMLDom);
pXMLDom->put_async(VARIANT_FALSE);
pXMLDom->put_validateOnParse(VARIANT_FALSE);
pXMLDom->put_resolveExternals(VARIANT_FALSE);
}
void Release()
{
if (pNode)
pNode->Release();
if (pXMLDom)
pXMLDom->Release();
CoUninitialize();
}
BOOL LoadXML(LPSTR lpzxml)
{
VARIANT_BOOL status;
BSTR bstrXML;
try{
bstrXML = _bstr_t(lpzxml);
pXMLDom->loadXML(bstrXML, &status);
return TRUE;
}
catch(...)
{
return FALSE;
}
}
BOOL Go_To_Parent(LPCSTR lpszNodeName)
{
IXMLDOMElement *pElementRoot;
IXMLDOMNodeList *pNodeList;
BSTRbstrValue;
long lNodeLength;
try{
pXMLDom->get_documentElement(&pElementRoot);
pElementRoot->getElementsByTagName(_bstr_t(lpszNodeName), &pNodeList);
pNodeList->get_length(&lNodeLength);
if (lNodeLength > 0)
{
pNodeList->get_item(0, &pNode);
return TRUE;
}
else
{
pXMLDom->get_childNodes(&pNodeList);
pNodeList->get_item(0, &pNode);
pNode->get_xml(&bstrValue);
return TRUE;
}
}
catch(...)
{
return FALSE;
}
}
BOOL GetChild(LPCSTR lpszNodeName, LPCSTR lpszNodeValue)
{
BSTR bstrValue;
long lLength, l;
IXMLDOMNodeList *pNodeList;
IXMLDOMNode *pLocalNode;
try{
pNode->get_childNodes(&pNodeList);
pNodeList->get_length(&lLength);
for (l=0;l <=lLength;l++)
{
pNodeList->get_item(l, &pLocalNode);
pLocalNode->get_xml(&bstrValue);
pLocalNode->get_nodeName(&bstrValue);
if (strcmp((LPSTR)_bstr_t(bstrValue), lpszNodeName) == 0)
{
pLocalNode->get_text(&bstrValue);
sprintf((char *)lpszNodeValue, "%s", (LPCSTR)_bstr_t(bstrValue));
return TRUE;
pNodeList->Release();
pLocalNode->Release();
}
}
return FALSE;
}
catch(...)
{
return FALSE;
}
}
void PrintMemoryInfo( DWORD processID, int Iteration )
{
HANDLE hProcess;
PROCESS_MEMORY_COUNTERS pmc;
TCHAR szProcessName[MAX_PATH] = TEXT("<unknown>");
hProcess = OpenProcess( PROCESS_QUERY_INFORMATION |
PROCESS_VM_READ,
FALSE, processID );
if (NULL == hProcess)
return;
HMODULE hMod;
DWORD cbNeeded;
if ( EnumProcessModules( hProcess, &hMod, sizeof(hMod),
&cbNeeded) )
{
GetModuleBaseName( hProcess, hMod, szProcessName,
sizeof(szProcessName)/sizeof(TCHAR) );
}
if ( GetProcessMemoryInfo( hProcess, &pmc, sizeof(pmc)) )
printf( "Iteration :%d, ProcessName :%s, PID:%u, Memory Usage:%dk
\n",Iteration, szProcessName, processID ,pmc.WorkingSetSize/1024);
CloseHandle( hProcess );
}
"Michael Rys [MSFT]" wrote:
> A repro is a T-SQL script, data and (if necessary) some code that shows the
> behaviour so we can try to run it in our environment to see whether we can
> find the reason for the behaviour (as per email exchange).
> Best regards
> Michael
> "TomPearson" <TomPearson@.discussions.microsoft.com> wrote in message
> news:567A03AE-AEF4-4147-A39E-6D2F9EADB4E3@.microsoft.com...
>
>
Wednesday, March 21, 2012
Indexing Service linked server
How can I setup a linked Indexing Service that is running on a remote machine (not on the same sqlserver machine).
We need to be able to link Indexing Service servers remotely from the sqlserver.
Thanks
M. Castellanos
you can't. Create a linked server to the local indexing service and then
query the catalog on the remote server like this
select * from openquery(LocalLinkedServer,'Select DocTitle, vpath, size,
create from RemoteServerName.RemoteCatalogName..Scope() where
contains(''test'')')
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"MC" <MC@.discussions.microsoft.com> wrote in message
news:DE637C5B-1EEB-4A31-8F11-0169F663A7A1@.microsoft.com...
> How can I setup a linked Indexing Service that is running on a remote
machine (not on the same sqlserver machine).
> We need to be able to link Indexing Service servers remotely from the
sqlserver.
> Thanks
> --
> M. Castellanos
We need to be able to link Indexing Service servers remotely from the sqlserver.
Thanks
M. Castellanos
you can't. Create a linked server to the local indexing service and then
query the catalog on the remote server like this
select * from openquery(LocalLinkedServer,'Select DocTitle, vpath, size,
create from RemoteServerName.RemoteCatalogName..Scope() where
contains(''test'')')
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"MC" <MC@.discussions.microsoft.com> wrote in message
news:DE637C5B-1EEB-4A31-8F11-0169F663A7A1@.microsoft.com...
> How can I setup a linked Indexing Service that is running on a remote
machine (not on the same sqlserver machine).
> We need to be able to link Indexing Service servers remotely from the
sqlserver.
> Thanks
> --
> M. Castellanos
Monday, March 19, 2012
Indexing on large table kills Transactional Replication
Hi,
SQL Server 2K Enterprise Ed, running Transactional replication, four
articles being replicated, 1 article containing over 15 million rows.
That table has 4 indexes on it. I created a job that executes a DBCC
INDEXDEFRAG statement against each of the indecies, I run the job at
the weekend, the job never errors, however it kills the Trans
replication.
What am I doing wrong? I used INDEXDEFRAG because of its online
capabilities, but the replication still fails.
Cheers
Scott
Are you having a problem with the Log Reader agent?
If so the problem is that Index Defragging is a logged operation and your
Tlog will balloon. This puts stress on your log reader agent and you will
see that it will experience time outs. The best way to fix this is to change
your Log Reader Agent's PollingInterval - set it to 1, and change the
ReadBatchSize - probably to 50 when you are doing the defragging. When you
not, use the defaults. Using profiles is an excellend way to do this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<quackhandle1975@.yahoo.co.uk> wrote in message
news:1108472684.342558.177210@.z14g2000cwz.googlegr oups.com...
> Hi,
> SQL Server 2K Enterprise Ed, running Transactional replication, four
> articles being replicated, 1 article containing over 15 million rows.
> That table has 4 indexes on it. I created a job that executes a DBCC
> INDEXDEFRAG statement against each of the indecies, I run the job at
> the weekend, the job never errors, however it kills the Trans
> replication.
> What am I doing wrong? I used INDEXDEFRAG because of its online
> capabilities, but the replication still fails.
> Cheers
> Scott
>
SQL Server 2K Enterprise Ed, running Transactional replication, four
articles being replicated, 1 article containing over 15 million rows.
That table has 4 indexes on it. I created a job that executes a DBCC
INDEXDEFRAG statement against each of the indecies, I run the job at
the weekend, the job never errors, however it kills the Trans
replication.
What am I doing wrong? I used INDEXDEFRAG because of its online
capabilities, but the replication still fails.
Cheers
Scott
Are you having a problem with the Log Reader agent?
If so the problem is that Index Defragging is a logged operation and your
Tlog will balloon. This puts stress on your log reader agent and you will
see that it will experience time outs. The best way to fix this is to change
your Log Reader Agent's PollingInterval - set it to 1, and change the
ReadBatchSize - probably to 50 when you are doing the defragging. When you
not, use the defaults. Using profiles is an excellend way to do this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<quackhandle1975@.yahoo.co.uk> wrote in message
news:1108472684.342558.177210@.z14g2000cwz.googlegr oups.com...
> Hi,
> SQL Server 2K Enterprise Ed, running Transactional replication, four
> articles being replicated, 1 article containing over 15 million rows.
> That table has 4 indexes on it. I created a job that executes a DBCC
> INDEXDEFRAG statement against each of the indecies, I run the job at
> the weekend, the job never errors, however it kills the Trans
> replication.
> What am I doing wrong? I used INDEXDEFRAG because of its online
> capabilities, but the replication still fails.
> Cheers
> Scott
>
Indexing issue, inconsistent performance
Hi -
Trying to chase down a baffling performance issue. Our database has been
running very slow lately. So we are performance tuning the database. In
doing so, we created a copy of our production database. In that database, I
changed one clustered index on a table to try to improve performance. I ran
one query - saw a slight improvement - but saw "lazy spool" in the execution
plan.
I tried to change it back to the original index by dropping the changed
index, and recreating the original index. I then ran the original query -
which now went from 5 seconds to 36 seconds.
I then ran DBCC REINDEX on that table. Performance of the query was still
markedly worse. I then reran the DBCC REINDEX on all tables, and then I
updated each tables statistics. Performance of that query has never returned
to the original 5 seconds.
What could be at issue here? Is there something else that I caused in
changing the index and changing it back?
Ideas much appreciated.You need to look at the query plan to see what it is doing. My guess is you
might be using a value the first time you call the query that forces table
scans.
--
Andrew J. Kelly SQL MVP
"Steph" <ss@.nospam.com> wrote in message
news:OY4MGMOMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> Hi -
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that database,
> I
> changed one clustered index on a table to try to improve performance. I
> ran
> one query - saw a slight improvement - but saw "lazy spool" in the
> execution
> plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was still
> markedly worse. I then reran the DBCC REINDEX on all tables, and then I
> updated each tables statistics. Performance of that query has never
> returned
> to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
>
>|||"Steph" <ss@.nospam.com> wrote in message
news:OY4MGMOMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> Hi -
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that database,
I
> changed one clustered index on a table to try to improve performance. I
ran
> one query - saw a slight improvement - but saw "lazy spool" in the
execution
> plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was still
> markedly worse. I then reran the DBCC REINDEX on all tables, and then I
> updated each tables statistics. Performance of that query has never
returned
> to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
>
I hope this is not a stupid question, but have you tried an UPDATE
STATISTICS and then running the query twice to check performance? I'm
assuming the query is SP or view, rather than passthrough|||Steph (ss@.nospam.com) writes:
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that
> database, I changed one clustered index on a table to try to improve
> performance. I ran one query - saw a slight improvement - but saw "lazy
> spool" in the execution plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was
> still markedly worse. I then reran the DBCC REINDEX on all tables, and
> then I updated each tables statistics. Performance of that query has
> never returned to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
Well, it could be that previously you had outdated statistics - which
this time caused SQL Server to draw the right conclusions. Once you
got the statistics up to date, the estimates came out wrong.
But without seeing queries, tables, index and query plans it's hard
to say. You will need to backtrack to the situation you started to
play with the index and compare the query plans.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Steph wrote:
> Hi -
> Trying to chase down a baffling performance issue. Our database has
> been running very slow lately. So we are performance tuning the
> database. In doing so, we created a copy of our production database.
> In that database, I changed one clustered index on a table to try to
> improve performance. I ran one query - saw a slight improvement - but
> saw "lazy spool" in the execution plan.
> I tried to change it back to the original index by dropping the
> changed index, and recreating the original index. I then ran the
> original query - which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was
> still markedly worse. I then reran the DBCC REINDEX on all tables,
> and then I updated each tables statistics. Performance of that query
> has never returned to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
Caching. Different execution plans due to differing filter criteria /
values...
robert
FUP to microsoft.public.sqlserver.server|||ouch.
i hate these.
this is real world stuff, and this happens more then you think.
Ultimately, the goal is to help the engine be teh most efficient. The
engine can be really obtuse, and sometimes you need to FORCE it to do
what is right over what it wants to do.
To do that, look at making things unique. Post some code and we can
help.
Trying to chase down a baffling performance issue. Our database has been
running very slow lately. So we are performance tuning the database. In
doing so, we created a copy of our production database. In that database, I
changed one clustered index on a table to try to improve performance. I ran
one query - saw a slight improvement - but saw "lazy spool" in the execution
plan.
I tried to change it back to the original index by dropping the changed
index, and recreating the original index. I then ran the original query -
which now went from 5 seconds to 36 seconds.
I then ran DBCC REINDEX on that table. Performance of the query was still
markedly worse. I then reran the DBCC REINDEX on all tables, and then I
updated each tables statistics. Performance of that query has never returned
to the original 5 seconds.
What could be at issue here? Is there something else that I caused in
changing the index and changing it back?
Ideas much appreciated.You need to look at the query plan to see what it is doing. My guess is you
might be using a value the first time you call the query that forces table
scans.
--
Andrew J. Kelly SQL MVP
"Steph" <ss@.nospam.com> wrote in message
news:OY4MGMOMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> Hi -
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that database,
> I
> changed one clustered index on a table to try to improve performance. I
> ran
> one query - saw a slight improvement - but saw "lazy spool" in the
> execution
> plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was still
> markedly worse. I then reran the DBCC REINDEX on all tables, and then I
> updated each tables statistics. Performance of that query has never
> returned
> to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
>
>|||"Steph" <ss@.nospam.com> wrote in message
news:OY4MGMOMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> Hi -
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that database,
I
> changed one clustered index on a table to try to improve performance. I
ran
> one query - saw a slight improvement - but saw "lazy spool" in the
execution
> plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was still
> markedly worse. I then reran the DBCC REINDEX on all tables, and then I
> updated each tables statistics. Performance of that query has never
returned
> to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
>
I hope this is not a stupid question, but have you tried an UPDATE
STATISTICS and then running the query twice to check performance? I'm
assuming the query is SP or view, rather than passthrough|||Steph (ss@.nospam.com) writes:
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that
> database, I changed one clustered index on a table to try to improve
> performance. I ran one query - saw a slight improvement - but saw "lazy
> spool" in the execution plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was
> still markedly worse. I then reran the DBCC REINDEX on all tables, and
> then I updated each tables statistics. Performance of that query has
> never returned to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
Well, it could be that previously you had outdated statistics - which
this time caused SQL Server to draw the right conclusions. Once you
got the statistics up to date, the estimates came out wrong.
But without seeing queries, tables, index and query plans it's hard
to say. You will need to backtrack to the situation you started to
play with the index and compare the query plans.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Steph wrote:
> Hi -
> Trying to chase down a baffling performance issue. Our database has
> been running very slow lately. So we are performance tuning the
> database. In doing so, we created a copy of our production database.
> In that database, I changed one clustered index on a table to try to
> improve performance. I ran one query - saw a slight improvement - but
> saw "lazy spool" in the execution plan.
> I tried to change it back to the original index by dropping the
> changed index, and recreating the original index. I then ran the
> original query - which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was
> still markedly worse. I then reran the DBCC REINDEX on all tables,
> and then I updated each tables statistics. Performance of that query
> has never returned to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
Caching. Different execution plans due to differing filter criteria /
values...
robert
FUP to microsoft.public.sqlserver.server|||ouch.
i hate these.
this is real world stuff, and this happens more then you think.
Ultimately, the goal is to help the engine be teh most efficient. The
engine can be really obtuse, and sometimes you need to FORCE it to do
what is right over what it wants to do.
To do that, look at making things unique. Post some code and we can
help.
Monday, March 12, 2012
indexing
I am running SQL 2000 and Crystal Reports ASP.
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skc
You should work with someone who has dome some indexing to learn the methods for doing this. You
create indexes to support the queries you submit. So the first part is to get to the SQL queries
(using profiler, for instance) and then try different indexing schemes to support those queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
|||where do I start then?
Also, I created a concatenated index (Manage indexes for
the tables) for cust_no and id. This has not speeded up
things.
skc
>--Original Message--
>You should work with someone who has dome some indexing
to learn the methods for doing this. You
>create indexes to support the queries you submit. So the
first part is to get to the SQL queries
>(using profiler, for instance) and then try different
indexing schemes to support those queries.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"skc" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
are[vbcol=seagreen]
then[vbcol=seagreen]
should[vbcol=seagreen]
application
>
>.
>
|||I would pick up a book that explain what indexes are, how they are used and how you use profiler to
catch the information you need to troubleshoot and tweak these situations. If you don't feel like
doing that, you can of course just catch the query in profiler, copy the text to Query Analyzer and
go from there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1dde801c45511$fbd10100$a601280a@.phx.gbl...[vbcol=seagreen]
> where do I start then?
> Also, I created a concatenated index (Manage indexes for
> the tables) for cust_no and id. This has not speeded up
> things.
> skc
> to learn the methods for doing this. You
> first part is to get to the SQL queries
> indexing schemes to support those queries.
> message
> are
> then
> should
> application
|||Like Tibor, I hesistate giving much specific advice with such a narrow view
of your world, But
Based on your info IF most customers have < 5% of the invoices in the
invoices table, and this is the only query you are interested in,,,
I might try cluster customer on ID, cluster invoices on custno...
Again, it might not be smart excluding the consideration of all of the other
possible queries which might exist on these tables.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skc
You should work with someone who has dome some indexing to learn the methods for doing this. You
create indexes to support the queries you submit. So the first part is to get to the SQL queries
(using profiler, for instance) and then try different indexing schemes to support those queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
|||where do I start then?
Also, I created a concatenated index (Manage indexes for
the tables) for cust_no and id. This has not speeded up
things.
skc
>--Original Message--
>You should work with someone who has dome some indexing
to learn the methods for doing this. You
>create indexes to support the queries you submit. So the
first part is to get to the SQL queries
>(using profiler, for instance) and then try different
indexing schemes to support those queries.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"skc" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
are[vbcol=seagreen]
then[vbcol=seagreen]
should[vbcol=seagreen]
application
>
>.
>
|||I would pick up a book that explain what indexes are, how they are used and how you use profiler to
catch the information you need to troubleshoot and tweak these situations. If you don't feel like
doing that, you can of course just catch the query in profiler, copy the text to Query Analyzer and
go from there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1dde801c45511$fbd10100$a601280a@.phx.gbl...[vbcol=seagreen]
> where do I start then?
> Also, I created a concatenated index (Manage indexes for
> the tables) for cust_no and id. This has not speeded up
> things.
> skc
> to learn the methods for doing this. You
> first part is to get to the SQL queries
> indexing schemes to support those queries.
> message
> are
> then
> should
> application
|||Like Tibor, I hesistate giving much specific advice with such a narrow view
of your world, But
Based on your info IF most customers have < 5% of the invoices in the
invoices table, and this is the only query you are interested in,,,
I might try cluster customer on ID, cluster invoices on custno...
Again, it might not be smart excluding the consideration of all of the other
possible queries which might exist on these tables.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
indexing
I am running SQL 2000 and Crystal Reports ASP.
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skc
Hi
Posting your DDL (Create table statements) is better than pseudo code!
I indexes can be created through enterprise manager using the buttons on the
table designer dialog. Hopefully you are maintaining your code in a source
code control system, therefore writing SQL directly.
For Create Index statements see Books Online or at
http://msdn.microsoft.com/library/de...asp?frame=true
If you wish to use SQL to do this.
If you have not created primary keys then look at the ALTER table statement
http://msdn.microsoft.com/library/de...sp?frame=true.
This statement can also be used to create a foreign key from the custno
column in the invoices table to the custno column in customers.
HTH
John
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e35501c45506$83d510c0$a401280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skc
Hi
Posting your DDL (Create table statements) is better than pseudo code!
I indexes can be created through enterprise manager using the buttons on the
table designer dialog. Hopefully you are maintaining your code in a source
code control system, therefore writing SQL directly.
For Create Index statements see Books Online or at
http://msdn.microsoft.com/library/de...asp?frame=true
If you wish to use SQL to do this.
If you have not created primary keys then look at the ALTER table statement
http://msdn.microsoft.com/library/de...sp?frame=true.
This statement can also be used to create a foreign key from the custno
column in the invoices table to the custno column in customers.
HTH
John
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e35501c45506$83d510c0$a401280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
Friday, March 9, 2012
indexing
I am running SQL 2000 and Crystal Reports ASP.
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skcYou should work with someone who has dome some indexing to learn the methods
for doing this. You
create indexes to support the queries you submit. So the first part is to ge
t to the SQL queries
(using profiler, for instance) and then try different indexing schemes to su
pport those queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx
.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc|||where do I start then?
Also, I created a concatenated index (Manage indexes for
the tables) for cust_no and id. This has not speeded up
things.
skc
>--Original Message--
>You should work with someone who has dome some indexing
to learn the methods for doing this. You
>create indexes to support the queries you submit. So the
first part is to get to the SQL queries
>(using profiler, for instance) and then try different
indexing schemes to support those queries.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"skc" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1e20b01c45506$91f0ab10$a101280a@.phx
.gbl...
are[vbcol=seagreen]
then[vbcol=seagreen]
should[vbcol=seagreen]
application[vbcol=seagreen]
>
>.
>|||I would pick up a book that explain what indexes are, how they are used and
how you use profiler to
catch the information you need to troubleshoot and tweak these situations. I
f you don't feel like
doing that, you can of course just catch the query in profiler, copy the tex
t to Query Analyzer and
go from there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1dde801c45511$fbd10100$a601280a@.phx
.gbl...[vbcol=seagreen]
> where do I start then?
> Also, I created a concatenated index (Manage indexes for
> the tables) for cust_no and id. This has not speeded up
> things.
> skc
>
> to learn the methods for doing this. You
> first part is to get to the SQL queries
> indexing schemes to support those queries.
> message
> are
> then
> should
> application|||Like Tibor, I hesistate giving much specific advice with such a narrow view
of your world, But
Based on your info IF most customers have < 5% of the invoices in the
invoices table, and this is the only query you are interested in,,,
I might try cluster customer on ID, cluster invoices on custno...
Again, it might not be smart excluding the consideration of all of the other
possible queries which might exist on these tables.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx
.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skcYou should work with someone who has dome some indexing to learn the methods
for doing this. You
create indexes to support the queries you submit. So the first part is to ge
t to the SQL queries
(using profiler, for instance) and then try different indexing schemes to su
pport those queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx
.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc|||where do I start then?
Also, I created a concatenated index (Manage indexes for
the tables) for cust_no and id. This has not speeded up
things.
skc
>--Original Message--
>You should work with someone who has dome some indexing
to learn the methods for doing this. You
>create indexes to support the queries you submit. So the
first part is to get to the SQL queries
>(using profiler, for instance) and then try different
indexing schemes to support those queries.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"skc" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1e20b01c45506$91f0ab10$a101280a@.phx
.gbl...
are[vbcol=seagreen]
then[vbcol=seagreen]
should[vbcol=seagreen]
application[vbcol=seagreen]
>
>.
>|||I would pick up a book that explain what indexes are, how they are used and
how you use profiler to
catch the information you need to troubleshoot and tweak these situations. I
f you don't feel like
doing that, you can of course just catch the query in profiler, copy the tex
t to Query Analyzer and
go from there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1dde801c45511$fbd10100$a601280a@.phx
.gbl...[vbcol=seagreen]
> where do I start then?
> Also, I created a concatenated index (Manage indexes for
> the tables) for cust_no and id. This has not speeded up
> things.
> skc
>
> to learn the methods for doing this. You
> first part is to get to the SQL queries
> indexing schemes to support those queries.
> message
> are
> then
> should
> application|||Like Tibor, I hesistate giving much specific advice with such a narrow view
of your world, But
Based on your info IF most customers have < 5% of the invoices in the
invoices table, and this is the only query you are interested in,,,
I might try cluster customer on ID, cluster invoices on custno...
Again, it might not be smart excluding the consideration of all of the other
possible queries which might exist on these tables.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx
.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
indexing
I am running SQL 2000 and Crystal Reports ASP.
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skcYou should work with someone who has dome some indexing to learn the methods for doing this. You
create indexes to support the queries you submit. So the first part is to get to the SQL queries
(using profiler, for instance) and then try different indexing schemes to support those queries.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc|||where do I start then?
Also, I created a concatenated index (Manage indexes for
the tables) for cust_no and id. This has not speeded up
things.
skc
>--Original Message--
>You should work with someone who has dome some indexing
to learn the methods for doing this. You
>create indexes to support the queries you submit. So the
first part is to get to the SQL queries
>(using profiler, for instance) and then try different
indexing schemes to support those queries.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"skc" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
>> I am running SQL 2000 and Crystal Reports ASP.
>> I have two tables called customer and invoice. There
are
>> 100000 customers and 7200000 invoices.
>> Here are the fields:
>> customer
>> - id ...1 to 100000
>> - custno ...1 to 100000
>> - name
>> - address1
>> - address2
>> - postcode
>> invoices
>> - id ...1 to 7200000
>> - custno ...random numbers below 100000
>> - inv_type
>> - amount
>> - invdate
>> The emphasis here is, if I type in a customer number
then
>> based on the customer numbers that match in the invoices
>> table invoices will appear.
>> I need to index both tables for quickness. I am new to
>> indexing, please help me!!! There are options for
>> clustered etc... and I do not know what to do. If I try
>> entering a customer number in Crystal Reports is takes
>> around 10 seconds to list around 20 invoices - this
should
>> be instantaneous as it is causing my .asp web
application
>> to time out.
>> Thanks,
>> skc
>
>.
>|||I would pick up a book that explain what indexes are, how they are used and how you use profiler to
catch the information you need to troubleshoot and tweak these situations. If you don't feel like
doing that, you can of course just catch the query in profiler, copy the text to Query Analyzer and
go from there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1dde801c45511$fbd10100$a601280a@.phx.gbl...
> where do I start then?
> Also, I created a concatenated index (Manage indexes for
> the tables) for cust_no and id. This has not speeded up
> things.
> skc
> >--Original Message--
> >You should work with someone who has dome some indexing
> to learn the methods for doing this. You
> >create indexes to support the queries you submit. So the
> first part is to get to the SQL queries
> >(using profiler, for instance) and then try different
> indexing schemes to support those queries.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"skc" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> >> I am running SQL 2000 and Crystal Reports ASP.
> >>
> >> I have two tables called customer and invoice. There
> are
> >> 100000 customers and 7200000 invoices.
> >>
> >> Here are the fields:
> >>
> >> customer
> >> - id ...1 to 100000
> >> - custno ...1 to 100000
> >> - name
> >> - address1
> >> - address2
> >> - postcode
> >>
> >> invoices
> >> - id ...1 to 7200000
> >> - custno ...random numbers below 100000
> >> - inv_type
> >> - amount
> >> - invdate
> >>
> >> The emphasis here is, if I type in a customer number
> then
> >> based on the customer numbers that match in the invoices
> >> table invoices will appear.
> >>
> >> I need to index both tables for quickness. I am new to
> >> indexing, please help me!!! There are options for
> >> clustered etc... and I do not know what to do. If I try
> >> entering a customer number in Crystal Reports is takes
> >> around 10 seconds to list around 20 invoices - this
> should
> >> be instantaneous as it is causing my .asp web
> application
> >> to time out.
> >>
> >> Thanks,
> >>
> >> skc
> >
> >
> >.
> >|||Like Tibor, I hesistate giving much specific advice with such a narrow view
of your world, But
Based on your info IF most customers have < 5% of the invoices in the
invoices table, and this is the only query you are interested in,,,
I might try cluster customer on ID, cluster invoices on custno...
Again, it might not be smart excluding the consideration of all of the other
possible queries which might exist on these tables.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skcYou should work with someone who has dome some indexing to learn the methods for doing this. You
create indexes to support the queries you submit. So the first part is to get to the SQL queries
(using profiler, for instance) and then try different indexing schemes to support those queries.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc|||where do I start then?
Also, I created a concatenated index (Manage indexes for
the tables) for cust_no and id. This has not speeded up
things.
skc
>--Original Message--
>You should work with someone who has dome some indexing
to learn the methods for doing this. You
>create indexes to support the queries you submit. So the
first part is to get to the SQL queries
>(using profiler, for instance) and then try different
indexing schemes to support those queries.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"skc" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
>> I am running SQL 2000 and Crystal Reports ASP.
>> I have two tables called customer and invoice. There
are
>> 100000 customers and 7200000 invoices.
>> Here are the fields:
>> customer
>> - id ...1 to 100000
>> - custno ...1 to 100000
>> - name
>> - address1
>> - address2
>> - postcode
>> invoices
>> - id ...1 to 7200000
>> - custno ...random numbers below 100000
>> - inv_type
>> - amount
>> - invdate
>> The emphasis here is, if I type in a customer number
then
>> based on the customer numbers that match in the invoices
>> table invoices will appear.
>> I need to index both tables for quickness. I am new to
>> indexing, please help me!!! There are options for
>> clustered etc... and I do not know what to do. If I try
>> entering a customer number in Crystal Reports is takes
>> around 10 seconds to list around 20 invoices - this
should
>> be instantaneous as it is causing my .asp web
application
>> to time out.
>> Thanks,
>> skc
>
>.
>|||I would pick up a book that explain what indexes are, how they are used and how you use profiler to
catch the information you need to troubleshoot and tweak these situations. If you don't feel like
doing that, you can of course just catch the query in profiler, copy the text to Query Analyzer and
go from there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1dde801c45511$fbd10100$a601280a@.phx.gbl...
> where do I start then?
> Also, I created a concatenated index (Manage indexes for
> the tables) for cust_no and id. This has not speeded up
> things.
> skc
> >--Original Message--
> >You should work with someone who has dome some indexing
> to learn the methods for doing this. You
> >create indexes to support the queries you submit. So the
> first part is to get to the SQL queries
> >(using profiler, for instance) and then try different
> indexing schemes to support those queries.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"skc" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> >> I am running SQL 2000 and Crystal Reports ASP.
> >>
> >> I have two tables called customer and invoice. There
> are
> >> 100000 customers and 7200000 invoices.
> >>
> >> Here are the fields:
> >>
> >> customer
> >> - id ...1 to 100000
> >> - custno ...1 to 100000
> >> - name
> >> - address1
> >> - address2
> >> - postcode
> >>
> >> invoices
> >> - id ...1 to 7200000
> >> - custno ...random numbers below 100000
> >> - inv_type
> >> - amount
> >> - invdate
> >>
> >> The emphasis here is, if I type in a customer number
> then
> >> based on the customer numbers that match in the invoices
> >> table invoices will appear.
> >>
> >> I need to index both tables for quickness. I am new to
> >> indexing, please help me!!! There are options for
> >> clustered etc... and I do not know what to do. If I try
> >> entering a customer number in Crystal Reports is takes
> >> around 10 seconds to list around 20 invoices - this
> should
> >> be instantaneous as it is causing my .asp web
> application
> >> to time out.
> >>
> >> Thanks,
> >>
> >> skc
> >
> >
> >.
> >|||Like Tibor, I hesistate giving much specific advice with such a narrow view
of your world, But
Based on your info IF most customers have < 5% of the invoices in the
invoices table, and this is the only query you are interested in,,,
I might try cluster customer on ID, cluster invoices on custno...
Again, it might not be smart excluding the consideration of all of the other
possible queries which might exist on these tables.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
indexes urgent please
HI
I created clustered index on 115 million records which runs 6 hours after i
created index now it is running for 16 minutes which is fine.
if i run that query statement(update statement) 1st time it will take 16
minutes and second time it will take 50 minutes
and third time it will take 1hour 30 minutes.this is weired can any one
please let me know what to do
again i am doing rebuilding and updatestats each time.
Thanks in advance
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200706/1
This is probably the wrong group for this, however you need to make sure
that you are not updating a clustered key as this will cause movement and/or
page splits in your clustered index. There may also be lots of
locking/blocking going on depending on how many rows you are updating.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"pardhiveswar via droptable.com" <u8956@.uwe> wrote in message
news:736fe741cfaeb@.uwe...
> HI
> I created clustered index on 115 million records which runs 6 hours after
> i
> created index now it is running for 16 minutes which is fine.
> if i run that query statement(update statement) 1st time it will take 16
> minutes and second time it will take 50 minutes
> and third time it will take 1hour 30 minutes.this is weired can any one
> please let me know what to do
> again i am doing rebuilding and updatestats each time.
> Thanks in advance
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200706/1
>
I created clustered index on 115 million records which runs 6 hours after i
created index now it is running for 16 minutes which is fine.
if i run that query statement(update statement) 1st time it will take 16
minutes and second time it will take 50 minutes
and third time it will take 1hour 30 minutes.this is weired can any one
please let me know what to do
again i am doing rebuilding and updatestats each time.
Thanks in advance
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200706/1
This is probably the wrong group for this, however you need to make sure
that you are not updating a clustered key as this will cause movement and/or
page splits in your clustered index. There may also be lots of
locking/blocking going on depending on how many rows you are updating.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"pardhiveswar via droptable.com" <u8956@.uwe> wrote in message
news:736fe741cfaeb@.uwe...
> HI
> I created clustered index on 115 million records which runs 6 hours after
> i
> created index now it is running for 16 minutes which is fine.
> if i run that query statement(update statement) 1st time it will take 16
> minutes and second time it will take 50 minutes
> and third time it will take 1hour 30 minutes.this is weired can any one
> please let me know what to do
> again i am doing rebuilding and updatestats each time.
> Thanks in advance
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200706/1
>
Wednesday, March 7, 2012
Indexes on Bulk Insert data
Any help would be appreciated.
I am running a script that does the following in succession.
1-Drop existing database and create new database
2-Defines tables, stored procedures and functions in the database
3-Imports data using bulk insert
4-Analyzes data using stored procedures
I would like to improve the performance of the analysis in step 4 by
creating indexes in step 2.
Question 1-Are indexes updated when data is bulk inserted? I know they are
when using normal insert, update, or delete T-SQL but I am not sure about
bulk insert of data.
Question 2-Do I need to update the index statistics in any way or would they
be ready to use in step 4.
Thanks,
CJI would define step 4 as create indexes, that will have your stats up to
date and save you from any performance issues during the load or having
to reindex or update the stats
I would do the analysis in step five (depending what type of analysis)
HTH
Ray Higdon MCSE, MCDBA, CCNA
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Chris (chris@.hrn.org) writes:
> 1-Drop existing database and create new database
> 2-Defines tables, stored procedures and functions in the database
> 3-Imports data using bulk insert
> 4-Analyzes data using stored procedures
> I would like to improve the performance of the analysis in step 4 by
> creating indexes in step 2.
> Question 1-Are indexes updated when data is bulk inserted? I know they are
> when using normal insert, update, or delete T-SQL but I am not sure about
> bulk insert of data.
Yes, they are. However, you may prefer to wait with creating indexes until
you have loaded the data for best performance. You may also opt to create
clustered indexes before bulk-loading and add non-clustered indexes after.
This is particularly appealing if the order in the data files corre-
sponds to the clustered indexes.
> Question 2-Do I need to update the index statistics in any way or would
> they be ready to use in step 4.
If you create indexes after bulk-loading, SQL Server will create statistics
for you when creating the indexes.
If you create indexex before bulk-loading, the statistics will not be
correct after the load. Thus, it can be a good idea run UPDATE STATISTICS
in this situation. However, if you don't, SQL Server will auto-update
statistics, unless you have turned off this feature.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Indexes not working after converting to 2000
I detached a SQL 7 DB, copied it to a server running SQL
2000 and attached it.
The indexes don't appear to be working. I've reindexed
the tables and executed a simple query that should
display the data in date order, but it's displayed in a
random order.
The same query on the original DB in SQL 7 shows the data
in date order.
Anyone know what's going on?
There's only one index. It's a nonclustered index on two
fields. Name+date.
Thanks,
DonThis is a multi-part message in MIME format.
--=_NextPart_000_02C4_01C3B29B.F1DDDD30
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Two things:
1. Order is not guaranteed, unless you include an ORDER BY in your
SELECT.
2. You should update statistics on all user tables (preferably WITH
FULLSCAN) when upgrading a database from 7.0 to 2000.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Don" <ddachner@.hotmail.com> wrote in message
news:047601c3b2c4$e3e7cfa0$a301280a@.phx.gbl...
I detached a SQL 7 DB, copied it to a server running SQL
2000 and attached it.
The indexes don't appear to be working. I've reindexed
the tables and executed a simple query that should
display the data in date order, but it's displayed in a
random order.
The same query on the original DB in SQL 7 shows the data
in date order.
Anyone know what's going on?
There's only one index. It's a nonclustered index on two
fields. Name+date.
Thanks,
Don
--=_NextPart_000_02C4_01C3B29B.F1DDDD30
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Two things:
1. Order is not =guaranteed, unless you include an ORDER BY in your SELECT.
2. You should update =statistics on all user tables (preferably WITH FULLSCAN) when upgrading a database =from 7.0 to 2000.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Don" wrote =in message news:047601c3b2c4$e3=e7cfa0$a301280a@.phx.gbl...I detached a SQL 7 DB, copied it to a server running SQL 2000 and =attached it.The indexes don't appear to be working. I've reindexed the =tables and executed a simple query that should display the data in date order, =but it's displayed in a random order. The same query on the original DB =in SQL 7 shows the data in date order.Anyone know what's going =on?There's only one index. It's a nonclustered index on two fields. Name+date.Thanks,Don
--=_NextPart_000_02C4_01C3B29B.F1DDDD30--|||Hi Don
The only queries that 'should' display data in a particular order are
queries that contain ORDER BY. If you were formerly getting data back in a
preferred order, you were just lucky. It was never documented or supported
that any query, using any index, would return data in any speficic order
without using ORDER BY.
OTOH, having a good index can help speed up the order by, and possibly make
a sort unnecessary, but the ORDER BY must be there in order to guarantee the
order of the rows in the result set.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Don" <ddachner@.hotmail.com> wrote in message
news:047601c3b2c4$e3e7cfa0$a301280a@.phx.gbl...
> I detached a SQL 7 DB, copied it to a server running SQL
> 2000 and attached it.
> The indexes don't appear to be working. I've reindexed
> the tables and executed a simple query that should
> display the data in date order, but it's displayed in a
> random order.
> The same query on the original DB in SQL 7 shows the data
> in date order.
> Anyone know what's going on?
> There's only one index. It's a nonclustered index on two
> fields. Name+date.
> Thanks,
> Don
>|||Thanks for you help. I didn't realize I was just being
lucky :-)
Don
>--Original Message--
>Hi Don
>The only queries that 'should' display data in a
particular order are
>queries that contain ORDER BY. If you were formerly
getting data back in a
>preferred order, you were just lucky. It was never
documented or supported
>that any query, using any index, would return data in
any speficic order
>without using ORDER BY.
>OTOH, having a good index can help speed up the order
by, and possibly make
>a sort unnecessary, but the ORDER BY must be there in
order to guarantee the
>order of the rows in the result set.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Don" <ddachner@.hotmail.com> wrote in message
>news:047601c3b2c4$e3e7cfa0$a301280a@.phx.gbl...
>> I detached a SQL 7 DB, copied it to a server running
SQL
>> 2000 and attached it.
>> The indexes don't appear to be working. I've reindexed
>> the tables and executed a simple query that should
>> display the data in date order, but it's displayed in a
>> random order.
>> The same query on the original DB in SQL 7 shows the
data
>> in date order.
>> Anyone know what's going on?
>> There's only one index. It's a nonclustered index on
two
>> fields. Name+date.
>> Thanks,
>> Don
>
>.
>|||I didn't know that about the ORDER BY.
Thanks for your help. I'll do the Scan update statistic.
Don
>--Original Message--
>Two things:
>1. Order is not guaranteed, unless you include an
ORDER BY in your
>SELECT.
>2. You should update statistics on all user tables
(preferably WITH
>FULLSCAN) when upgrading a database from 7.0 to 2000.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Don" <ddachner@.hotmail.com> wrote in message
>news:047601c3b2c4$e3e7cfa0$a301280a@.phx.gbl...
>I detached a SQL 7 DB, copied it to a server running SQL
>2000 and attached it.
>The indexes don't appear to be working. I've reindexed
>the tables and executed a simple query that should
>display the data in date order, but it's displayed in a
>random order.
>The same query on the original DB in SQL 7 shows the data
>in date order.
>Anyone know what's going on?
>There's only one index. It's a nonclustered index on two
>fields. Name+date.
>Thanks,
>Don
>
2000 and attached it.
The indexes don't appear to be working. I've reindexed
the tables and executed a simple query that should
display the data in date order, but it's displayed in a
random order.
The same query on the original DB in SQL 7 shows the data
in date order.
Anyone know what's going on?
There's only one index. It's a nonclustered index on two
fields. Name+date.
Thanks,
DonThis is a multi-part message in MIME format.
--=_NextPart_000_02C4_01C3B29B.F1DDDD30
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Two things:
1. Order is not guaranteed, unless you include an ORDER BY in your
SELECT.
2. You should update statistics on all user tables (preferably WITH
FULLSCAN) when upgrading a database from 7.0 to 2000.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Don" <ddachner@.hotmail.com> wrote in message
news:047601c3b2c4$e3e7cfa0$a301280a@.phx.gbl...
I detached a SQL 7 DB, copied it to a server running SQL
2000 and attached it.
The indexes don't appear to be working. I've reindexed
the tables and executed a simple query that should
display the data in date order, but it's displayed in a
random order.
The same query on the original DB in SQL 7 shows the data
in date order.
Anyone know what's going on?
There's only one index. It's a nonclustered index on two
fields. Name+date.
Thanks,
Don
--=_NextPart_000_02C4_01C3B29B.F1DDDD30
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Two things:
1. Order is not =guaranteed, unless you include an ORDER BY in your SELECT.
2. You should update =statistics on all user tables (preferably WITH FULLSCAN) when upgrading a database =from 7.0 to 2000.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Don"
--=_NextPart_000_02C4_01C3B29B.F1DDDD30--|||Hi Don
The only queries that 'should' display data in a particular order are
queries that contain ORDER BY. If you were formerly getting data back in a
preferred order, you were just lucky. It was never documented or supported
that any query, using any index, would return data in any speficic order
without using ORDER BY.
OTOH, having a good index can help speed up the order by, and possibly make
a sort unnecessary, but the ORDER BY must be there in order to guarantee the
order of the rows in the result set.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Don" <ddachner@.hotmail.com> wrote in message
news:047601c3b2c4$e3e7cfa0$a301280a@.phx.gbl...
> I detached a SQL 7 DB, copied it to a server running SQL
> 2000 and attached it.
> The indexes don't appear to be working. I've reindexed
> the tables and executed a simple query that should
> display the data in date order, but it's displayed in a
> random order.
> The same query on the original DB in SQL 7 shows the data
> in date order.
> Anyone know what's going on?
> There's only one index. It's a nonclustered index on two
> fields. Name+date.
> Thanks,
> Don
>|||Thanks for you help. I didn't realize I was just being
lucky :-)
Don
>--Original Message--
>Hi Don
>The only queries that 'should' display data in a
particular order are
>queries that contain ORDER BY. If you were formerly
getting data back in a
>preferred order, you were just lucky. It was never
documented or supported
>that any query, using any index, would return data in
any speficic order
>without using ORDER BY.
>OTOH, having a good index can help speed up the order
by, and possibly make
>a sort unnecessary, but the ORDER BY must be there in
order to guarantee the
>order of the rows in the result set.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Don" <ddachner@.hotmail.com> wrote in message
>news:047601c3b2c4$e3e7cfa0$a301280a@.phx.gbl...
>> I detached a SQL 7 DB, copied it to a server running
SQL
>> 2000 and attached it.
>> The indexes don't appear to be working. I've reindexed
>> the tables and executed a simple query that should
>> display the data in date order, but it's displayed in a
>> random order.
>> The same query on the original DB in SQL 7 shows the
data
>> in date order.
>> Anyone know what's going on?
>> There's only one index. It's a nonclustered index on
two
>> fields. Name+date.
>> Thanks,
>> Don
>
>.
>|||I didn't know that about the ORDER BY.
Thanks for your help. I'll do the Scan update statistic.
Don
>--Original Message--
>Two things:
>1. Order is not guaranteed, unless you include an
ORDER BY in your
>SELECT.
>2. You should update statistics on all user tables
(preferably WITH
>FULLSCAN) when upgrading a database from 7.0 to 2000.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Don" <ddachner@.hotmail.com> wrote in message
>news:047601c3b2c4$e3e7cfa0$a301280a@.phx.gbl...
>I detached a SQL 7 DB, copied it to a server running SQL
>2000 and attached it.
>The indexes don't appear to be working. I've reindexed
>the tables and executed a simple query that should
>display the data in date order, but it's displayed in a
>random order.
>The same query on the original DB in SQL 7 shows the data
>in date order.
>Anyone know what's going on?
>There's only one index. It's a nonclustered index on two
>fields. Name+date.
>Thanks,
>Don
>
Subscribe to:
Posts (Atom)