This has happened several times. We set up a data driven subscription and it
runs fine. We can edit it a few times and it works fine afterwards also.
But at some point after perhaps editing it 5 or 6 times it jsut stops
working. The job never fires off like it wasn't scheduled. You can delete
it and recreate it, then it works fine again. There doesn't seem to be a
concrete pattern to it. It seems like the subscription jsut becomes
corrupted or something
Has anyone encountered a problem like this?Hi,
When i had tested data driven subscriptions i had changed the schedule say
at least about 25 times or more, i had not faced any issues.
One observation i had was if the time set for the schedule is the same as
the current computer time on click finish it does not run immediately. The
schedule time should always be a future time.
Regards,
Pugaz
"sebring1130" wrote:
> This has happened several times. We set up a data driven subscription and it
> runs fine. We can edit it a few times and it works fine afterwards also.
> But at some point after perhaps editing it 5 or 6 times it jsut stops
> working. The job never fires off like it wasn't scheduled. You can delete
> it and recreate it, then it works fine again. There doesn't seem to be a
> concrete pattern to it. It seems like the subscription jsut becomes
> corrupted or something
> Has anyone encountered a problem like this?|||I figured out what is causing this. When you first create a data-driven sub.
it works fine. Later, however, if you edit it to re-run it you run into a
problem if the subscription is set to run on a one-time basis only. You
select "one time" as the schedule and set the time you want it to fire off.
However you can't enter a date. Instead of picking up today as the default
like it does when you first create the subscription, it keeps the same date
that you originally used when you first set it up. So if you are editing a
one-time scheduled subscription and you didn't create the subscription that
same day, the schedule you just created is already in the past and thus never
fires. You can confirm this by checking the schedule table. A workaround is
to update the table manually...
Update Schedule
Set StartDate = '2004-11-10 14:10'
Where ScheduleID = 'whatever'
"sebring1130" wrote:
> This has happened several times. We set up a data driven subscription and it
> runs fine. We can edit it a few times and it works fine afterwards also.
> But at some point after perhaps editing it 5 or 6 times it jsut stops
> working. The job never fires off like it wasn't scheduled. You can delete
> it and recreate it, then it works fine again. There doesn't seem to be a
> concrete pattern to it. It seems like the subscription jsut becomes
> corrupted or something
> Has anyone encountered a problem like this?
Showing posts with label runs. Show all posts
Showing posts with label runs. Show all posts
Sunday, March 25, 2012
Monday, March 19, 2012
Data Conversion supported on Standard Edition?
I created an Integration Services Package that runs fine from my local computer using BIDS. However when I imported into our SQL Server and try to run it from there I get the following error:
DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Data Conversion"
We are running SQL Server 2005 Standard Edition 64-bit.
We have integration services installed on the server. Is data conversion something that is not supported on Standard Edition?
Also have a similar message for "Send Mail Task."
Is there anywhere that outlines what features are supported on each version?
What version of SQL Server are you running on your developer's workstation?|||I'm running BIDS on my workstation which is connecting to our SQL Server. The same SQL Server where the integration package will not run from when imported into. So I'm actually not running a full blown SQL Server database on my workstation, just using BIDS on it along with Studio.
The Management Studio on my workstation is 9.00.3042.
SQL Server Integration Services is 9.00.3042. (Found by going to About > Help in Visual Studio)
Our SQL Server version is 9.00.3050
|||So both your workstation and the server are running SQL Server Standard edition? Not developer/enterprise edition?|||Server is definitely Standard Edition. When I installed the workstation components on my workstation I honestly don't remember if I installed them from the Standard edition or Developer edition. Most likely I installed the workstation components using the Developer edition. Is there a way to check?
I guess that would explain why it works on my workstation but not on the server?
|||
Erikk Ross wrote:
Server is definitely Standard Edition. When I installed the workstation components on my workstation I honestly don't remember if I installed them from the Standard edition or Developer edition. Most likely I installed the workstation components using the Developer edition. Is there a way to check?
Run this query on the server and again on your local version:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')|||
Yeah, like I said I don't have the actual Database engine installed on my workstation. But our server version is SP2: 9.00.3050.
So I guess Data Conversion is not supported in Standard Edition? Or send mail? Is there a matrix somewhere that shows what features in Integration Services are supported in each version of SQL Server? It would seem to me that data conversion is something that is pretty common...I'm a little suprised it would require the Enterprise edition.
|||Alright, well, I do think it's because you're developing a package in a Developer environment, which is a higher level than the Standard edition that your server runs. If your server was an Enterprise version, you wouldn't have a problem (of course).Uninstall and reinstall the SSIS components from the Standard Edition CDs and you should be fine. The Data Conversion component should work in the Standard Edition.|||
Alright, well, I do think it's because you're developing a package in a Developer environment, which is a higher level than the Standard edition that your server runs. If your server was an Enterprise version, you wouldn't have a problem (of course). Uninstall and reinstall the SSIS components from the Standard Edition CDs and you should be fine. The Data Conversion component should work in the Standard Edition.
That was indeed the solution. I just did a quick test and creating the package from a Standard edition version did allow the data conversion to work correctly. Thank you!!
|||Well, after uninstalling SQL Server workstation components on my development machine and reinstalling the Standard version it did not fix my problem. Any package created on my local development machine still does not work when imported into SQL Server. Get the same Product level to low error.
At least the good news is that I can create packages on the SQL Server itself and they seem to work fine. My best guess is that uninstalling the developer edition and reinstalling the standard edition just wasn't enough. I would suspect that if I was to completely wipe my machine clean then install Standard Edition it would probably be ok. But that is more hassle than it's worth.
|||
Well, after uninstalling SQL Server workstation components on my development machine and reinstalling the Standard version it did not fix my problem. Any package created on my local development machine still does not work when imported into SQL Server. Get the same Product level to low error.
At least the good news is that I can create packages on the SQL Server itself and they seem to work fine. My best guess is that uninstalling the developer edition and reinstalling the standard edition just wasn't enough. I would suspect that if I was to completely wipe my machine clean then install Standard Edition it would probably be ok. But that is more hassle than it's worth.
Any NEW packages created still don't work?|||
Correct, I created a brand new package. Well I first tried to rebuild my old package but that didn't work either, so I just created a new one. Same problems, runs fine on my local workstation, but when imported into SQL Server gives the same error.
I am currently installing standard edition workstation components on a new workstation with Visual Studio and will try to create a package from there and import it and see if that works.
|||Run this on the server, please:SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')|||
Phil Brammer wrote:
Run this on the server, please:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
9.00.3050.00 SP2 Standard Edition (64-bit)
Well now I am completely lost. I just installed the standard edition workstation components on a brand new machine. A machine that has never had SQL Server installed on it. I created a new package in Visual Studio, imported it into our SQL Server, ran it, and still get the same ProductLevelToLow errors.
Error: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Data Conversion".
|||After reading this I realized my problem: http://blogs.msdn.com/michen/archive/2006/08/11/package-exec-location.aspx
I was running the package from my workstation using Management Studio, unaware that the package was actually running on my local workstation and not on the server. Since it was running on my workstation it required Integration Services to be installed which it wasn't, so that is why I got those error messages. As soon as I ran it directly from the server it worked fine.
*sigh* I wish this was more obvious. I thought that by running it in Management Studio it was just automatically running it on the server.
Friday, February 24, 2012
data "movement" the culprit?
sql2k sp3
I have a report query that sometimes runs in 3 seconds, other times I kill
it after 10 minutes. When its taking a long time, there is no no blocking, no
extremely high cpu/ memory usage, no huge amount of activity. Something I
just noticed though is that someone put the Clustered Index on a date filed
even though this is an OLTP DB. Is there any possibility that when its taking
a long time to return results, whats happening is that data was recently
inserted that would cause the data in the table to move because of where the
clustering is? That SQL is searching for a moving target based on the data
range of the SP, which is in the middle of being rearranged because of it's
clustering setup? If this were the case, would it cause actual blocking(dont
forget Im not seeing any.)? I know I'm grasping here, but dont know what else
could cause this.
TIA, ChrisR
It's unlikely that is the cause. A page split if it occurs should only take
ms not minutes. And you would see blocking. My guess is you have a bad query
plan and disk bottlenecks. But the only way to be sure is to monitor the
server from all aspects to see what is the bottleneck. What about wait's
during that time?
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
> sql2k sp3
> I have a report query that sometimes runs in 3 seconds, other times I kill
> it after 10 minutes. When its taking a long time, there is no no blocking,
> no
> extremely high cpu/ memory usage, no huge amount of activity. Something I
> just noticed though is that someone put the Clustered Index on a date
> filed
> even though this is an OLTP DB. Is there any possibility that when its
> taking
> a long time to return results, whats happening is that data was recently
> inserted that would cause the data in the table to move because of where
> the
> clustering is? That SQL is searching for a moving target based on the data
> range of the SP, which is in the middle of being rearranged because of
> it's
> clustering setup? If this were the case, would it cause actual
> blocking(dont
> forget Im not seeing any.)? I know I'm grasping here, but dont know what
> else
> could cause this.
> TIA, ChrisR
|||I havent looked, but will. Does this number represent how long it take SQL to
satisfy a query? Im doing my testing in Query Analyzer so I can already tell
that.
"Andrew J. Kelly" wrote:
> It's unlikely that is the cause. A page split if it occurs should only take
> ms not minutes. And you would see blocking. My guess is you have a bad query
> plan and disk bottlenecks. But the only way to be sure is to monitor the
> server from all aspects to see what is the bottleneck. What about wait's
> during that time?
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
>
>
|||Waits tell you allot about what sql server is waiting on most. See here for
more details:
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:90871384-25AD-485B-BB51-B623B8671647@.microsoft.com...[vbcol=seagreen]
>I havent looked, but will. Does this number represent how long it take SQL
>to
> satisfy a query? Im doing my testing in Query Analyzer so I can already
> tell
> that.
> "Andrew J. Kelly" wrote:
I have a report query that sometimes runs in 3 seconds, other times I kill
it after 10 minutes. When its taking a long time, there is no no blocking, no
extremely high cpu/ memory usage, no huge amount of activity. Something I
just noticed though is that someone put the Clustered Index on a date filed
even though this is an OLTP DB. Is there any possibility that when its taking
a long time to return results, whats happening is that data was recently
inserted that would cause the data in the table to move because of where the
clustering is? That SQL is searching for a moving target based on the data
range of the SP, which is in the middle of being rearranged because of it's
clustering setup? If this were the case, would it cause actual blocking(dont
forget Im not seeing any.)? I know I'm grasping here, but dont know what else
could cause this.
TIA, ChrisR
It's unlikely that is the cause. A page split if it occurs should only take
ms not minutes. And you would see blocking. My guess is you have a bad query
plan and disk bottlenecks. But the only way to be sure is to monitor the
server from all aspects to see what is the bottleneck. What about wait's
during that time?
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
> sql2k sp3
> I have a report query that sometimes runs in 3 seconds, other times I kill
> it after 10 minutes. When its taking a long time, there is no no blocking,
> no
> extremely high cpu/ memory usage, no huge amount of activity. Something I
> just noticed though is that someone put the Clustered Index on a date
> filed
> even though this is an OLTP DB. Is there any possibility that when its
> taking
> a long time to return results, whats happening is that data was recently
> inserted that would cause the data in the table to move because of where
> the
> clustering is? That SQL is searching for a moving target based on the data
> range of the SP, which is in the middle of being rearranged because of
> it's
> clustering setup? If this were the case, would it cause actual
> blocking(dont
> forget Im not seeing any.)? I know I'm grasping here, but dont know what
> else
> could cause this.
> TIA, ChrisR
|||I havent looked, but will. Does this number represent how long it take SQL to
satisfy a query? Im doing my testing in Query Analyzer so I can already tell
that.
"Andrew J. Kelly" wrote:
> It's unlikely that is the cause. A page split if it occurs should only take
> ms not minutes. And you would see blocking. My guess is you have a bad query
> plan and disk bottlenecks. But the only way to be sure is to monitor the
> server from all aspects to see what is the bottleneck. What about wait's
> during that time?
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
>
>
|||Waits tell you allot about what sql server is waiting on most. See here for
more details:
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:90871384-25AD-485B-BB51-B623B8671647@.microsoft.com...[vbcol=seagreen]
>I havent looked, but will. Does this number represent how long it take SQL
>to
> satisfy a query? Im doing my testing in Query Analyzer so I can already
> tell
> that.
> "Andrew J. Kelly" wrote:
data "movement" the culprit?
sql2k sp3
I have a report query that sometimes runs in 3 seconds, other times I kill
it after 10 minutes. When its taking a long time, there is no no blocking, no
extremely high cpu/ memory usage, no huge amount of activity. Something I
just noticed though is that someone put the Clustered Index on a date filed
even though this is an OLTP DB. Is there any possibility that when its taking
a long time to return results, whats happening is that data was recently
inserted that would cause the data in the table to move because of where the
clustering is? That SQL is searching for a moving target based on the data
range of the SP, which is in the middle of being rearranged because of it's
clustering setup? If this were the case, would it cause actual blocking(dont
forget Im not seeing any.)? I know I'm grasping here, but dont know what else
could cause this.
TIA, ChrisRIt's unlikely that is the cause. A page split if it occurs should only take
ms not minutes. And you would see blocking. My guess is you have a bad query
plan and disk bottlenecks. But the only way to be sure is to monitor the
server from all aspects to see what is the bottleneck. What about wait's
during that time?
--
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
> sql2k sp3
> I have a report query that sometimes runs in 3 seconds, other times I kill
> it after 10 minutes. When its taking a long time, there is no no blocking,
> no
> extremely high cpu/ memory usage, no huge amount of activity. Something I
> just noticed though is that someone put the Clustered Index on a date
> filed
> even though this is an OLTP DB. Is there any possibility that when its
> taking
> a long time to return results, whats happening is that data was recently
> inserted that would cause the data in the table to move because of where
> the
> clustering is? That SQL is searching for a moving target based on the data
> range of the SP, which is in the middle of being rearranged because of
> it's
> clustering setup? If this were the case, would it cause actual
> blocking(dont
> forget Im not seeing any.)? I know I'm grasping here, but dont know what
> else
> could cause this.
> TIA, ChrisR|||I havent looked, but will. Does this number represent how long it take SQL to
satisfy a query? Im doing my testing in Query Analyzer so I can already tell
that.
"Andrew J. Kelly" wrote:
> It's unlikely that is the cause. A page split if it occurs should only take
> ms not minutes. And you would see blocking. My guess is you have a bad query
> plan and disk bottlenecks. But the only way to be sure is to monitor the
> server from all aspects to see what is the bottleneck. What about wait's
> during that time?
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
> > sql2k sp3
> >
> > I have a report query that sometimes runs in 3 seconds, other times I kill
> > it after 10 minutes. When its taking a long time, there is no no blocking,
> > no
> > extremely high cpu/ memory usage, no huge amount of activity. Something I
> > just noticed though is that someone put the Clustered Index on a date
> > filed
> > even though this is an OLTP DB. Is there any possibility that when its
> > taking
> > a long time to return results, whats happening is that data was recently
> > inserted that would cause the data in the table to move because of where
> > the
> > clustering is? That SQL is searching for a moving target based on the data
> > range of the SP, which is in the middle of being rearranged because of
> > it's
> > clustering setup? If this were the case, would it cause actual
> > blocking(dont
> > forget Im not seeing any.)? I know I'm grasping here, but dont know what
> > else
> > could cause this.
> >
> > TIA, ChrisR
>
>|||Waits tell you allot about what sql server is waiting on most. See here for
more details:
http://sqldev.net/misc/WaitTypes.htm Wait Types
--
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:90871384-25AD-485B-BB51-B623B8671647@.microsoft.com...
>I havent looked, but will. Does this number represent how long it take SQL
>to
> satisfy a query? Im doing my testing in Query Analyzer so I can already
> tell
> that.
> "Andrew J. Kelly" wrote:
>> It's unlikely that is the cause. A page split if it occurs should only
>> take
>> ms not minutes. And you would see blocking. My guess is you have a bad
>> query
>> plan and disk bottlenecks. But the only way to be sure is to monitor the
>> server from all aspects to see what is the bottleneck. What about wait's
>> during that time?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
>> > sql2k sp3
>> >
>> > I have a report query that sometimes runs in 3 seconds, other times I
>> > kill
>> > it after 10 minutes. When its taking a long time, there is no no
>> > blocking,
>> > no
>> > extremely high cpu/ memory usage, no huge amount of activity. Something
>> > I
>> > just noticed though is that someone put the Clustered Index on a date
>> > filed
>> > even though this is an OLTP DB. Is there any possibility that when its
>> > taking
>> > a long time to return results, whats happening is that data was
>> > recently
>> > inserted that would cause the data in the table to move because of
>> > where
>> > the
>> > clustering is? That SQL is searching for a moving target based on the
>> > data
>> > range of the SP, which is in the middle of being rearranged because of
>> > it's
>> > clustering setup? If this were the case, would it cause actual
>> > blocking(dont
>> > forget Im not seeing any.)? I know I'm grasping here, but dont know
>> > what
>> > else
>> > could cause this.
>> >
>> > TIA, ChrisR
>>
I have a report query that sometimes runs in 3 seconds, other times I kill
it after 10 minutes. When its taking a long time, there is no no blocking, no
extremely high cpu/ memory usage, no huge amount of activity. Something I
just noticed though is that someone put the Clustered Index on a date filed
even though this is an OLTP DB. Is there any possibility that when its taking
a long time to return results, whats happening is that data was recently
inserted that would cause the data in the table to move because of where the
clustering is? That SQL is searching for a moving target based on the data
range of the SP, which is in the middle of being rearranged because of it's
clustering setup? If this were the case, would it cause actual blocking(dont
forget Im not seeing any.)? I know I'm grasping here, but dont know what else
could cause this.
TIA, ChrisRIt's unlikely that is the cause. A page split if it occurs should only take
ms not minutes. And you would see blocking. My guess is you have a bad query
plan and disk bottlenecks. But the only way to be sure is to monitor the
server from all aspects to see what is the bottleneck. What about wait's
during that time?
--
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
> sql2k sp3
> I have a report query that sometimes runs in 3 seconds, other times I kill
> it after 10 minutes. When its taking a long time, there is no no blocking,
> no
> extremely high cpu/ memory usage, no huge amount of activity. Something I
> just noticed though is that someone put the Clustered Index on a date
> filed
> even though this is an OLTP DB. Is there any possibility that when its
> taking
> a long time to return results, whats happening is that data was recently
> inserted that would cause the data in the table to move because of where
> the
> clustering is? That SQL is searching for a moving target based on the data
> range of the SP, which is in the middle of being rearranged because of
> it's
> clustering setup? If this were the case, would it cause actual
> blocking(dont
> forget Im not seeing any.)? I know I'm grasping here, but dont know what
> else
> could cause this.
> TIA, ChrisR|||I havent looked, but will. Does this number represent how long it take SQL to
satisfy a query? Im doing my testing in Query Analyzer so I can already tell
that.
"Andrew J. Kelly" wrote:
> It's unlikely that is the cause. A page split if it occurs should only take
> ms not minutes. And you would see blocking. My guess is you have a bad query
> plan and disk bottlenecks. But the only way to be sure is to monitor the
> server from all aspects to see what is the bottleneck. What about wait's
> during that time?
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
> > sql2k sp3
> >
> > I have a report query that sometimes runs in 3 seconds, other times I kill
> > it after 10 minutes. When its taking a long time, there is no no blocking,
> > no
> > extremely high cpu/ memory usage, no huge amount of activity. Something I
> > just noticed though is that someone put the Clustered Index on a date
> > filed
> > even though this is an OLTP DB. Is there any possibility that when its
> > taking
> > a long time to return results, whats happening is that data was recently
> > inserted that would cause the data in the table to move because of where
> > the
> > clustering is? That SQL is searching for a moving target based on the data
> > range of the SP, which is in the middle of being rearranged because of
> > it's
> > clustering setup? If this were the case, would it cause actual
> > blocking(dont
> > forget Im not seeing any.)? I know I'm grasping here, but dont know what
> > else
> > could cause this.
> >
> > TIA, ChrisR
>
>|||Waits tell you allot about what sql server is waiting on most. See here for
more details:
http://sqldev.net/misc/WaitTypes.htm Wait Types
--
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:90871384-25AD-485B-BB51-B623B8671647@.microsoft.com...
>I havent looked, but will. Does this number represent how long it take SQL
>to
> satisfy a query? Im doing my testing in Query Analyzer so I can already
> tell
> that.
> "Andrew J. Kelly" wrote:
>> It's unlikely that is the cause. A page split if it occurs should only
>> take
>> ms not minutes. And you would see blocking. My guess is you have a bad
>> query
>> plan and disk bottlenecks. But the only way to be sure is to monitor the
>> server from all aspects to see what is the bottleneck. What about wait's
>> during that time?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
>> > sql2k sp3
>> >
>> > I have a report query that sometimes runs in 3 seconds, other times I
>> > kill
>> > it after 10 minutes. When its taking a long time, there is no no
>> > blocking,
>> > no
>> > extremely high cpu/ memory usage, no huge amount of activity. Something
>> > I
>> > just noticed though is that someone put the Clustered Index on a date
>> > filed
>> > even though this is an OLTP DB. Is there any possibility that when its
>> > taking
>> > a long time to return results, whats happening is that data was
>> > recently
>> > inserted that would cause the data in the table to move because of
>> > where
>> > the
>> > clustering is? That SQL is searching for a moving target based on the
>> > data
>> > range of the SP, which is in the middle of being rearranged because of
>> > it's
>> > clustering setup? If this were the case, would it cause actual
>> > blocking(dont
>> > forget Im not seeing any.)? I know I'm grasping here, but dont know
>> > what
>> > else
>> > could cause this.
>> >
>> > TIA, ChrisR
>>
data "movement" the culprit?
sql2k sp3
I have a report query that sometimes runs in 3 seconds, other times I kill
it after 10 minutes. When its taking a long time, there is no no blocking, n
o
extremely high cpu/ memory usage, no huge amount of activity. Something I
just noticed though is that someone put the Clustered Index on a date filed
even though this is an OLTP DB. Is there any possibility that when its takin
g
a long time to return results, whats happening is that data was recently
inserted that would cause the data in the table to move because of where the
clustering is? That SQL is searching for a moving target based on the data
range of the SP, which is in the middle of being rearranged because of it's
clustering setup? If this were the case, would it cause actual blocking(dont
forget Im not seeing any.)? I know I'm grasping here, but dont know what els
e
could cause this.
TIA, ChrisRIt's unlikely that is the cause. A page split if it occurs should only take
ms not minutes. And you would see blocking. My guess is you have a bad query
plan and disk bottlenecks. But the only way to be sure is to monitor the
server from all aspects to see what is the bottleneck. What about wait's
during that time?
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
> sql2k sp3
> I have a report query that sometimes runs in 3 seconds, other times I kill
> it after 10 minutes. When its taking a long time, there is no no blocking,
> no
> extremely high cpu/ memory usage, no huge amount of activity. Something I
> just noticed though is that someone put the Clustered Index on a date
> filed
> even though this is an OLTP DB. Is there any possibility that when its
> taking
> a long time to return results, whats happening is that data was recently
> inserted that would cause the data in the table to move because of where
> the
> clustering is? That SQL is searching for a moving target based on the data
> range of the SP, which is in the middle of being rearranged because of
> it's
> clustering setup? If this were the case, would it cause actual
> blocking(dont
> forget Im not seeing any.)? I know I'm grasping here, but dont know what
> else
> could cause this.
> TIA, ChrisR|||I havent looked, but will. Does this number represent how long it take SQL t
o
satisfy a query? Im doing my testing in Query Analyzer so I can already tell
that.
"Andrew J. Kelly" wrote:
> It's unlikely that is the cause. A page split if it occurs should only tak
e
> ms not minutes. And you would see blocking. My guess is you have a bad que
ry
> plan and disk bottlenecks. But the only way to be sure is to monitor the
> server from all aspects to see what is the bottleneck. What about wait's
> during that time?
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
>
>|||Waits tell you allot about what sql server is waiting on most. See here for
more details:
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:90871384-25AD-485B-BB51-B623B8671647@.microsoft.com...[vbcol=seagreen]
>I havent looked, but will. Does this number represent how long it take SQL
>to
> satisfy a query? Im doing my testing in Query Analyzer so I can already
> tell
> that.
> "Andrew J. Kelly" wrote:
>
I have a report query that sometimes runs in 3 seconds, other times I kill
it after 10 minutes. When its taking a long time, there is no no blocking, n
o
extremely high cpu/ memory usage, no huge amount of activity. Something I
just noticed though is that someone put the Clustered Index on a date filed
even though this is an OLTP DB. Is there any possibility that when its takin
g
a long time to return results, whats happening is that data was recently
inserted that would cause the data in the table to move because of where the
clustering is? That SQL is searching for a moving target based on the data
range of the SP, which is in the middle of being rearranged because of it's
clustering setup? If this were the case, would it cause actual blocking(dont
forget Im not seeing any.)? I know I'm grasping here, but dont know what els
e
could cause this.
TIA, ChrisRIt's unlikely that is the cause. A page split if it occurs should only take
ms not minutes. And you would see blocking. My guess is you have a bad query
plan and disk bottlenecks. But the only way to be sure is to monitor the
server from all aspects to see what is the bottleneck. What about wait's
during that time?
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
> sql2k sp3
> I have a report query that sometimes runs in 3 seconds, other times I kill
> it after 10 minutes. When its taking a long time, there is no no blocking,
> no
> extremely high cpu/ memory usage, no huge amount of activity. Something I
> just noticed though is that someone put the Clustered Index on a date
> filed
> even though this is an OLTP DB. Is there any possibility that when its
> taking
> a long time to return results, whats happening is that data was recently
> inserted that would cause the data in the table to move because of where
> the
> clustering is? That SQL is searching for a moving target based on the data
> range of the SP, which is in the middle of being rearranged because of
> it's
> clustering setup? If this were the case, would it cause actual
> blocking(dont
> forget Im not seeing any.)? I know I'm grasping here, but dont know what
> else
> could cause this.
> TIA, ChrisR|||I havent looked, but will. Does this number represent how long it take SQL t
o
satisfy a query? Im doing my testing in Query Analyzer so I can already tell
that.
"Andrew J. Kelly" wrote:
> It's unlikely that is the cause. A page split if it occurs should only tak
e
> ms not minutes. And you would see blocking. My guess is you have a bad que
ry
> plan and disk bottlenecks. But the only way to be sure is to monitor the
> server from all aspects to see what is the bottleneck. What about wait's
> during that time?
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
>
>|||Waits tell you allot about what sql server is waiting on most. See here for
more details:
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:90871384-25AD-485B-BB51-B623B8671647@.microsoft.com...[vbcol=seagreen]
>I havent looked, but will. Does this number represent how long it take SQL
>to
> satisfy a query? Im doing my testing in Query Analyzer so I can already
> tell
> that.
> "Andrew J. Kelly" wrote:
>
Sunday, February 19, 2012
Daily scheduled job has its schedule disabled despite successful completion
I have a job which is scheduled to run once daily. The job is enabled and the schedule is also enabled. After the job runs (successfully), the schedule has its 'Enabled' flag reset (i.e. the 'Enabled' checkbox in the schedule properties has become unchecked) and so is not rescheduled.
I have other jobs configured in a similar way, but they are run and then rescheduled in the normal way without any problems.
What could be going on?I'm sure you'd know but there is definately no disables step in the job?
e.g
EXEC sp_update_job @.job_name = 'bla bla',
@.enabled = 0|||This may be of some use to you. Have you checked the logs?
http://support.microsoft.com/default.aspx?scid=kb;en-us;295378&Product=sql2k
I have other jobs configured in a similar way, but they are run and then rescheduled in the normal way without any problems.
What could be going on?I'm sure you'd know but there is definately no disables step in the job?
e.g
EXEC sp_update_job @.job_name = 'bla bla',
@.enabled = 0|||This may be of some use to you. Have you checked the logs?
http://support.microsoft.com/default.aspx?scid=kb;en-us;295378&Product=sql2k
Daily jobs do not resubmit after failure
I have a Microsoft SQL 2000 server which early each morning runs a host of jobs which append transaction data to a transaction data warehouse. On a Saturday morning the 8th the jobs failed to execute due to a network failure and according to the SQL Enterprise\SQL Server Agent\Jobs window the next run date was the 9th. Well it's Monday the 10th, the jobs have not attempted to run since the 8th and it still shows then next run date as the 9th. The Server Agent is running and the daily SQL backups have continued to run as scheduled. Does anyone know why the jobs, scheduled to run daily, are not at least attempting to do so?
Thanks for your helpHowdy
WHat version of Service Pack are you running on the server? Should be 3A if you can...
Cheers,
SG.
Thanks for your helpHowdy
WHat version of Service Pack are you running on the server? Should be 3A if you can...
Cheers,
SG.
Daily job runs slow after upgrade to 2005.
We have a process that uses XMLSpy to import an xml file of products/pricing
daily. We receive this file through an ftp site. This process usually ran
in 15-20 min with SQL 2000. It is now running about 2 1/2 hours which is
unacceptable. Any ideas what could be so different?
The database used is set to compatibility level 90, simple recovery mode,
ansi defaults. We've tried turning off create and udpate of statistics as
this is temporary data used for updating other tables. Nothing seems to
make a difference. I just bet there's something simple I'm missing here..
There is only about 8 tables, 14k-15k rows each and no indexes anywhere we
add those after the import.
Many thanks for any input !!That's not much to go on Tim. I don't know what this XMLSpy does but even 15
minutes is way to long a time to import just a few thousand rows. I would
think 15 seconds would be too long. Can you give some more details on
exactly what it is doing? Do you have SET NOCOUNT ON in the job step?
--
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23yy6klgoGHA.5084@.TK2MSFTNGP03.phx.gbl...
> We have a process that uses XMLSpy to import an xml file of
> products/pricing daily. We receive this file through an ftp site. This
> process usually ran in 15-20 min with SQL 2000. It is now running about 2
> 1/2 hours which is unacceptable. Any ideas what could be so different?
> The database used is set to compatibility level 90, simple recovery mode,
> ansi defaults. We've tried turning off create and udpate of statistics as
> this is temporary data used for updating other tables. Nothing seems to
> make a difference. I just bet there's something simple I'm missing here..
> There is only about 8 tables, 14k-15k rows each and no indexes anywhere we
> add those after the import.
> Many thanks for any input !!
>|||We receive product updates in an XML file. We use XMLSpy because the
company we receive the file from does...just so we can skip any
inconsistencies. XMLSpy uses either SQLOLEDB or SQL Native Client
connection and then creates the necessary tables in an empty database. It
then loads all the product rows from the XML file into the respective
tables. 15 minutes was reasonable we think as XMLSpy is doing an enourmous
amount of string processing and then inserting rows from a workstation into
the server.
XMLSpy is also deriving keys from related data that we specify. These keys
are included in the data but they are just integer columns at that point.
We create the actual indexes after the import is done. This is just another
reason for 15 minutes vs 15 seconds.
At any rate, it does boil down to just reading through an XML file and
inserting rows into a table. I'm not wanting to be skimpy on details but
that's about all there is to it. This isn't a procedure I have control of,
it is a 3rd party COM object we call into to do the import so I cannot
address the NOCOUNT issue. I do not believe it to be the 3rd parties issue
either as it worked fine until now. We've tried this on both SQL2k5 32 and
64 bit servers with the same results.
I've double-checked the recovery method is set to simple...compatibility is
90. Even turned off auto create/update statistics. Would it help any to
use bulk recovery mode? We don't need ANY logging of this data it is
completely transient in nature.
Thanks for responding!!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OjwfA7hoGHA.148@.TK2MSFTNGP04.phx.gbl...
> That's not much to go on Tim. I don't know what this XMLSpy does but even
> 15 minutes is way to long a time to import just a few thousand rows. I
> would think 15 seconds would be too long. Can you give some more details
> on exactly what it is doing? Do you have SET NOCOUNT ON in the job step?
> --
> Andrew J. Kelly SQL MVP
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23yy6klgoGHA.5084@.TK2MSFTNGP03.phx.gbl...
>> We have a process that uses XMLSpy to import an xml file of
>> products/pricing daily. We receive this file through an ftp site. This
>> process usually ran in 15-20 min with SQL 2000. It is now running about
>> 2 1/2 hours which is unacceptable. Any ideas what could be so different?
>> The database used is set to compatibility level 90, simple recovery mode,
>> ansi defaults. We've tried turning off create and udpate of statistics
>> as this is temporary data used for updating other tables. Nothing seems
>> to make a difference. I just bet there's something simple I'm missing
>> here..
>> There is only about 8 tables, 14k-15k rows each and no indexes anywhere
>> we add those after the import.
>> Many thanks for any input !!
>|||Tim Greenwood wrote:
> We have a process that uses XMLSpy to import an xml file of products/pricing
> daily. We receive this file through an ftp site. This process usually ran
> in 15-20 min with SQL 2000. It is now running about 2 1/2 hours which is
> unacceptable. Any ideas what could be so different?
> The database used is set to compatibility level 90, simple recovery mode,
> ansi defaults. We've tried turning off create and udpate of statistics as
> this is temporary data used for updating other tables. Nothing seems to
> make a difference. I just bet there's something simple I'm missing here..
> There is only about 8 tables, 14k-15k rows each and no indexes anywhere we
> add those after the import.
> Many thanks for any input !!
>
Ignoring the XML aspect for now, start with the basics, look at Perfmon
while this process is running. Look at Avg disk queue lengths, CPU %.
What other databases are hosted on this machine? Is the transaction log
on the same volume as the data file? Where is TEMPDB? Multi-processor
machine?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Is this a new server or OS as well? Is this SQLSpy running ont he same
server as SQL Server? If so are you sure there is enough memory for both?
Have you looked at profiler and perfmon to see what may be going on? You
need to narrow down the possibilities otherwise it's really hard to say.
These may help:
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance Audit
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
--
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:OuMtLKioGHA.4024@.TK2MSFTNGP03.phx.gbl...
> We receive product updates in an XML file. We use XMLSpy because the
> company we receive the file from does...just so we can skip any
> inconsistencies. XMLSpy uses either SQLOLEDB or SQL Native Client
> connection and then creates the necessary tables in an empty database. It
> then loads all the product rows from the XML file into the respective
> tables. 15 minutes was reasonable we think as XMLSpy is doing an
> enourmous amount of string processing and then inserting rows from a
> workstation into the server.
> XMLSpy is also deriving keys from related data that we specify. These
> keys are included in the data but they are just integer columns at that
> point. We create the actual indexes after the import is done. This is
> just another reason for 15 minutes vs 15 seconds.
> At any rate, it does boil down to just reading through an XML file and
> inserting rows into a table. I'm not wanting to be skimpy on details but
> that's about all there is to it. This isn't a procedure I have control
> of, it is a 3rd party COM object we call into to do the import so I cannot
> address the NOCOUNT issue. I do not believe it to be the 3rd parties
> issue either as it worked fine until now. We've tried this on both SQL2k5
> 32 and 64 bit servers with the same results.
> I've double-checked the recovery method is set to simple...compatibility
> is 90. Even turned off auto create/update statistics. Would it help any
> to use bulk recovery mode? We don't need ANY logging of this data it is
> completely transient in nature.
> Thanks for responding!!
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OjwfA7hoGHA.148@.TK2MSFTNGP04.phx.gbl...
>> That's not much to go on Tim. I don't know what this XMLSpy does but even
>> 15 minutes is way to long a time to import just a few thousand rows. I
>> would think 15 seconds would be too long. Can you give some more details
>> on exactly what it is doing? Do you have SET NOCOUNT ON in the job step?
>> --
>> Andrew J. Kelly SQL MVP
>> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
>> news:%23yy6klgoGHA.5084@.TK2MSFTNGP03.phx.gbl...
>> We have a process that uses XMLSpy to import an xml file of
>> products/pricing daily. We receive this file through an ftp site. This
>> process usually ran in 15-20 min with SQL 2000. It is now running about
>> 2 1/2 hours which is unacceptable. Any ideas what could be so
>> different?
>> The database used is set to compatibility level 90, simple recovery
>> mode, ansi defaults. We've tried turning off create and udpate of
>> statistics as this is temporary data used for updating other tables.
>> Nothing seems to make a difference. I just bet there's something simple
>> I'm missing here..
>> There is only about 8 tables, 14k-15k rows each and no indexes anywhere
>> we add those after the import.
>> Many thanks for any input !!
>>
>|||Yes I will run profiler on monday while this is running. To answer your
questions...we have 7 db's on this server. All tlogs are on their own
mirrored set of spindles and tempdb is on it's own mirrored set as well. It
is a dual 64-bit machine. 64bit sql2005 and 64bit windows server 2003
enterprise.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:OY4gPrioGHA.4036@.TK2MSFTNGP05.phx.gbl...
> Tim Greenwood wrote:
>> We have a process that uses XMLSpy to import an xml file of
>> products/pricing daily. We receive this file through an ftp site. This
>> process usually ran in 15-20 min with SQL 2000. It is now running about
>> 2 1/2 hours which is unacceptable. Any ideas what could be so different?
>> The database used is set to compatibility level 90, simple recovery mode,
>> ansi defaults. We've tried turning off create and udpate of statistics
>> as this is temporary data used for updating other tables. Nothing seems
>> to make a difference. I just bet there's something simple I'm missing
>> here..
>> There is only about 8 tables, 14k-15k rows each and no indexes anywhere
>> we add those after the import.
>> Many thanks for any input !!
> Ignoring the XML aspect for now, start with the basics, look at Perfmon
> while this process is running. Look at Avg disk queue lengths, CPU %.
> What other databases are hosted on this machine? Is the transaction log
> on the same volume as the data file? Where is TEMPDB? Multi-processor
> machine?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
daily. We receive this file through an ftp site. This process usually ran
in 15-20 min with SQL 2000. It is now running about 2 1/2 hours which is
unacceptable. Any ideas what could be so different?
The database used is set to compatibility level 90, simple recovery mode,
ansi defaults. We've tried turning off create and udpate of statistics as
this is temporary data used for updating other tables. Nothing seems to
make a difference. I just bet there's something simple I'm missing here..
There is only about 8 tables, 14k-15k rows each and no indexes anywhere we
add those after the import.
Many thanks for any input !!That's not much to go on Tim. I don't know what this XMLSpy does but even 15
minutes is way to long a time to import just a few thousand rows. I would
think 15 seconds would be too long. Can you give some more details on
exactly what it is doing? Do you have SET NOCOUNT ON in the job step?
--
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23yy6klgoGHA.5084@.TK2MSFTNGP03.phx.gbl...
> We have a process that uses XMLSpy to import an xml file of
> products/pricing daily. We receive this file through an ftp site. This
> process usually ran in 15-20 min with SQL 2000. It is now running about 2
> 1/2 hours which is unacceptable. Any ideas what could be so different?
> The database used is set to compatibility level 90, simple recovery mode,
> ansi defaults. We've tried turning off create and udpate of statistics as
> this is temporary data used for updating other tables. Nothing seems to
> make a difference. I just bet there's something simple I'm missing here..
> There is only about 8 tables, 14k-15k rows each and no indexes anywhere we
> add those after the import.
> Many thanks for any input !!
>|||We receive product updates in an XML file. We use XMLSpy because the
company we receive the file from does...just so we can skip any
inconsistencies. XMLSpy uses either SQLOLEDB or SQL Native Client
connection and then creates the necessary tables in an empty database. It
then loads all the product rows from the XML file into the respective
tables. 15 minutes was reasonable we think as XMLSpy is doing an enourmous
amount of string processing and then inserting rows from a workstation into
the server.
XMLSpy is also deriving keys from related data that we specify. These keys
are included in the data but they are just integer columns at that point.
We create the actual indexes after the import is done. This is just another
reason for 15 minutes vs 15 seconds.
At any rate, it does boil down to just reading through an XML file and
inserting rows into a table. I'm not wanting to be skimpy on details but
that's about all there is to it. This isn't a procedure I have control of,
it is a 3rd party COM object we call into to do the import so I cannot
address the NOCOUNT issue. I do not believe it to be the 3rd parties issue
either as it worked fine until now. We've tried this on both SQL2k5 32 and
64 bit servers with the same results.
I've double-checked the recovery method is set to simple...compatibility is
90. Even turned off auto create/update statistics. Would it help any to
use bulk recovery mode? We don't need ANY logging of this data it is
completely transient in nature.
Thanks for responding!!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OjwfA7hoGHA.148@.TK2MSFTNGP04.phx.gbl...
> That's not much to go on Tim. I don't know what this XMLSpy does but even
> 15 minutes is way to long a time to import just a few thousand rows. I
> would think 15 seconds would be too long. Can you give some more details
> on exactly what it is doing? Do you have SET NOCOUNT ON in the job step?
> --
> Andrew J. Kelly SQL MVP
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23yy6klgoGHA.5084@.TK2MSFTNGP03.phx.gbl...
>> We have a process that uses XMLSpy to import an xml file of
>> products/pricing daily. We receive this file through an ftp site. This
>> process usually ran in 15-20 min with SQL 2000. It is now running about
>> 2 1/2 hours which is unacceptable. Any ideas what could be so different?
>> The database used is set to compatibility level 90, simple recovery mode,
>> ansi defaults. We've tried turning off create and udpate of statistics
>> as this is temporary data used for updating other tables. Nothing seems
>> to make a difference. I just bet there's something simple I'm missing
>> here..
>> There is only about 8 tables, 14k-15k rows each and no indexes anywhere
>> we add those after the import.
>> Many thanks for any input !!
>|||Tim Greenwood wrote:
> We have a process that uses XMLSpy to import an xml file of products/pricing
> daily. We receive this file through an ftp site. This process usually ran
> in 15-20 min with SQL 2000. It is now running about 2 1/2 hours which is
> unacceptable. Any ideas what could be so different?
> The database used is set to compatibility level 90, simple recovery mode,
> ansi defaults. We've tried turning off create and udpate of statistics as
> this is temporary data used for updating other tables. Nothing seems to
> make a difference. I just bet there's something simple I'm missing here..
> There is only about 8 tables, 14k-15k rows each and no indexes anywhere we
> add those after the import.
> Many thanks for any input !!
>
Ignoring the XML aspect for now, start with the basics, look at Perfmon
while this process is running. Look at Avg disk queue lengths, CPU %.
What other databases are hosted on this machine? Is the transaction log
on the same volume as the data file? Where is TEMPDB? Multi-processor
machine?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Is this a new server or OS as well? Is this SQLSpy running ont he same
server as SQL Server? If so are you sure there is enough memory for both?
Have you looked at profiler and perfmon to see what may be going on? You
need to narrow down the possibilities otherwise it's really hard to say.
These may help:
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance Audit
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
--
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:OuMtLKioGHA.4024@.TK2MSFTNGP03.phx.gbl...
> We receive product updates in an XML file. We use XMLSpy because the
> company we receive the file from does...just so we can skip any
> inconsistencies. XMLSpy uses either SQLOLEDB or SQL Native Client
> connection and then creates the necessary tables in an empty database. It
> then loads all the product rows from the XML file into the respective
> tables. 15 minutes was reasonable we think as XMLSpy is doing an
> enourmous amount of string processing and then inserting rows from a
> workstation into the server.
> XMLSpy is also deriving keys from related data that we specify. These
> keys are included in the data but they are just integer columns at that
> point. We create the actual indexes after the import is done. This is
> just another reason for 15 minutes vs 15 seconds.
> At any rate, it does boil down to just reading through an XML file and
> inserting rows into a table. I'm not wanting to be skimpy on details but
> that's about all there is to it. This isn't a procedure I have control
> of, it is a 3rd party COM object we call into to do the import so I cannot
> address the NOCOUNT issue. I do not believe it to be the 3rd parties
> issue either as it worked fine until now. We've tried this on both SQL2k5
> 32 and 64 bit servers with the same results.
> I've double-checked the recovery method is set to simple...compatibility
> is 90. Even turned off auto create/update statistics. Would it help any
> to use bulk recovery mode? We don't need ANY logging of this data it is
> completely transient in nature.
> Thanks for responding!!
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OjwfA7hoGHA.148@.TK2MSFTNGP04.phx.gbl...
>> That's not much to go on Tim. I don't know what this XMLSpy does but even
>> 15 minutes is way to long a time to import just a few thousand rows. I
>> would think 15 seconds would be too long. Can you give some more details
>> on exactly what it is doing? Do you have SET NOCOUNT ON in the job step?
>> --
>> Andrew J. Kelly SQL MVP
>> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
>> news:%23yy6klgoGHA.5084@.TK2MSFTNGP03.phx.gbl...
>> We have a process that uses XMLSpy to import an xml file of
>> products/pricing daily. We receive this file through an ftp site. This
>> process usually ran in 15-20 min with SQL 2000. It is now running about
>> 2 1/2 hours which is unacceptable. Any ideas what could be so
>> different?
>> The database used is set to compatibility level 90, simple recovery
>> mode, ansi defaults. We've tried turning off create and udpate of
>> statistics as this is temporary data used for updating other tables.
>> Nothing seems to make a difference. I just bet there's something simple
>> I'm missing here..
>> There is only about 8 tables, 14k-15k rows each and no indexes anywhere
>> we add those after the import.
>> Many thanks for any input !!
>>
>|||Yes I will run profiler on monday while this is running. To answer your
questions...we have 7 db's on this server. All tlogs are on their own
mirrored set of spindles and tempdb is on it's own mirrored set as well. It
is a dual 64-bit machine. 64bit sql2005 and 64bit windows server 2003
enterprise.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:OY4gPrioGHA.4036@.TK2MSFTNGP05.phx.gbl...
> Tim Greenwood wrote:
>> We have a process that uses XMLSpy to import an xml file of
>> products/pricing daily. We receive this file through an ftp site. This
>> process usually ran in 15-20 min with SQL 2000. It is now running about
>> 2 1/2 hours which is unacceptable. Any ideas what could be so different?
>> The database used is set to compatibility level 90, simple recovery mode,
>> ansi defaults. We've tried turning off create and udpate of statistics
>> as this is temporary data used for updating other tables. Nothing seems
>> to make a difference. I just bet there's something simple I'm missing
>> here..
>> There is only about 8 tables, 14k-15k rows each and no indexes anywhere
>> we add those after the import.
>> Many thanks for any input !!
> Ignoring the XML aspect for now, start with the basics, look at Perfmon
> while this process is running. Look at Avg disk queue lengths, CPU %.
> What other databases are hosted on this machine? Is the transaction log
> on the same volume as the data file? Where is TEMPDB? Multi-processor
> machine?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Daily job runs slow after upgrade to 2005.
We have a process that uses XMLSpy to import an xml file of products/pricing
daily. We receive this file through an ftp site. This process usually ran
in 15-20 min with SQL 2000. It is now running about 2 1/2 hours which is
unacceptable. Any ideas what could be so different?
The database used is set to compatibility level 90, simple recovery mode,
ansi defaults. We've tried turning off create and udpate of statistics as
this is temporary data used for updating other tables. Nothing seems to
make a difference. I just bet there's something simple I'm missing here..
There is only about 8 tables, 14k-15k rows each and no indexes anywhere we
add those after the import.
Many thanks for any input !!That's not much to go on Tim. I don't know what this XMLSpy does but even 15
minutes is way to long a time to import just a few thousand rows. I would
think 15 seconds would be too long. Can you give some more details on
exactly what it is doing? Do you have SET NOCOUNT ON in the job step?
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23yy6klgoGHA.5084@.TK2MSFTNGP03.phx.gbl...
> We have a process that uses XMLSpy to import an xml file of
> products/pricing daily. We receive this file through an ftp site. This
> process usually ran in 15-20 min with SQL 2000. It is now running about 2
> 1/2 hours which is unacceptable. Any ideas what could be so different?
> The database used is set to compatibility level 90, simple recovery mode,
> ansi defaults. We've tried turning off create and udpate of statistics as
> this is temporary data used for updating other tables. Nothing seems to
> make a difference. I just bet there's something simple I'm missing here..
> There is only about 8 tables, 14k-15k rows each and no indexes anywhere we
> add those after the import.
> Many thanks for any input !!
>|||We receive product updates in an XML file. We use XMLSpy because the
company we receive the file from does...just so we can skip any
inconsistencies. XMLSpy uses either SQLOLEDB or SQL Native Client
connection and then creates the necessary tables in an empty database. It
then loads all the product rows from the XML file into the respective
tables. 15 minutes was reasonable we think as XMLSpy is doing an enourmous
amount of string processing and then inserting rows from a workstation into
the server.
XMLSpy is also deriving keys from related data that we specify. These keys
are included in the data but they are just integer columns at that point.
We create the actual indexes after the import is done. This is just another
reason for 15 minutes vs 15 seconds.
At any rate, it does boil down to just reading through an XML file and
inserting rows into a table. I'm not wanting to be skimpy on details but
that's about all there is to it. This isn't a procedure I have control of,
it is a 3rd party COM object we call into to do the import so I cannot
address the NOCOUNT issue. I do not believe it to be the 3rd parties issue
either as it worked fine until now. We've tried this on both SQL2k5 32 and
64 bit servers with the same results.
I've double-checked the recovery method is set to simple...compatibility is
90. Even turned off auto create/update statistics. Would it help any to
use bulk recovery mode? We don't need ANY logging of this data it is
completely transient in nature.
Thanks for responding!!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OjwfA7hoGHA.148@.TK2MSFTNGP04.phx.gbl...
> That's not much to go on Tim. I don't know what this XMLSpy does but even
> 15 minutes is way to long a time to import just a few thousand rows. I
> would think 15 seconds would be too long. Can you give some more details
> on exactly what it is doing? Do you have SET NOCOUNT ON in the job step?
> --
> Andrew J. Kelly SQL MVP
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23yy6klgoGHA.5084@.TK2MSFTNGP03.phx.gbl...
>|||Tim Greenwood wrote:
> We have a process that uses XMLSpy to import an xml file of products/prici
ng
> daily. We receive this file through an ftp site. This process usually ra
n
> in 15-20 min with SQL 2000. It is now running about 2 1/2 hours which is
> unacceptable. Any ideas what could be so different?
> The database used is set to compatibility level 90, simple recovery mode,
> ansi defaults. We've tried turning off create and udpate of statistics as
> this is temporary data used for updating other tables. Nothing seems to
> make a difference. I just bet there's something simple I'm missing here..
> There is only about 8 tables, 14k-15k rows each and no indexes anywhere we
> add those after the import.
> Many thanks for any input !!
>
Ignoring the XML aspect for now, start with the basics, look at Perfmon
while this process is running. Look at Avg disk queue lengths, CPU %.
What other databases are hosted on this machine? Is the transaction log
on the same volume as the data file? Where is TEMPDB? Multi-processor
machine?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Is this a new server or OS as well? Is this SQLSpy running ont he same
server as SQL Server? If so are you sure there is enough memory for both?
Have you looked at profiler and perfmon to see what may be going on? You
need to narrow down the possibilities otherwise it's really hard to say.
These may help:
http://www.sql-server-performance.c...nce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pr...perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:OuMtLKioGHA.4024@.TK2MSFTNGP03.phx.gbl...
> We receive product updates in an XML file. We use XMLSpy because the
> company we receive the file from does...just so we can skip any
> inconsistencies. XMLSpy uses either SQLOLEDB or SQL Native Client
> connection and then creates the necessary tables in an empty database. It
> then loads all the product rows from the XML file into the respective
> tables. 15 minutes was reasonable we think as XMLSpy is doing an
> enourmous amount of string processing and then inserting rows from a
> workstation into the server.
> XMLSpy is also deriving keys from related data that we specify. These
> keys are included in the data but they are just integer columns at that
> point. We create the actual indexes after the import is done. This is
> just another reason for 15 minutes vs 15 seconds.
> At any rate, it does boil down to just reading through an XML file and
> inserting rows into a table. I'm not wanting to be skimpy on details but
> that's about all there is to it. This isn't a procedure I have control
> of, it is a 3rd party COM object we call into to do the import so I cannot
> address the NOCOUNT issue. I do not believe it to be the 3rd parties
> issue either as it worked fine until now. We've tried this on both SQL2k5
> 32 and 64 bit servers with the same results.
> I've double-checked the recovery method is set to simple...compatibility
> is 90. Even turned off auto create/update statistics. Would it help any
> to use bulk recovery mode? We don't need ANY logging of this data it is
> completely transient in nature.
> Thanks for responding!!
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OjwfA7hoGHA.148@.TK2MSFTNGP04.phx.gbl...
>|||Yes I will run profiler on monday while this is running. To answer your
questions...we have 7 db's on this server. All tlogs are on their own
mirrored set of spindles and tempdb is on it's own mirrored set as well. It
is a dual 64-bit machine. 64bit sql2005 and 64bit windows server 2003
enterprise.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:OY4gPrioGHA.4036@.TK2MSFTNGP05.phx.gbl...
> Tim Greenwood wrote:
> Ignoring the XML aspect for now, start with the basics, look at Perfmon
> while this process is running. Look at Avg disk queue lengths, CPU %.
> What other databases are hosted on this machine? Is the transaction log
> on the same volume as the data file? Where is TEMPDB? Multi-processor
> machine?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
daily. We receive this file through an ftp site. This process usually ran
in 15-20 min with SQL 2000. It is now running about 2 1/2 hours which is
unacceptable. Any ideas what could be so different?
The database used is set to compatibility level 90, simple recovery mode,
ansi defaults. We've tried turning off create and udpate of statistics as
this is temporary data used for updating other tables. Nothing seems to
make a difference. I just bet there's something simple I'm missing here..
There is only about 8 tables, 14k-15k rows each and no indexes anywhere we
add those after the import.
Many thanks for any input !!That's not much to go on Tim. I don't know what this XMLSpy does but even 15
minutes is way to long a time to import just a few thousand rows. I would
think 15 seconds would be too long. Can you give some more details on
exactly what it is doing? Do you have SET NOCOUNT ON in the job step?
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23yy6klgoGHA.5084@.TK2MSFTNGP03.phx.gbl...
> We have a process that uses XMLSpy to import an xml file of
> products/pricing daily. We receive this file through an ftp site. This
> process usually ran in 15-20 min with SQL 2000. It is now running about 2
> 1/2 hours which is unacceptable. Any ideas what could be so different?
> The database used is set to compatibility level 90, simple recovery mode,
> ansi defaults. We've tried turning off create and udpate of statistics as
> this is temporary data used for updating other tables. Nothing seems to
> make a difference. I just bet there's something simple I'm missing here..
> There is only about 8 tables, 14k-15k rows each and no indexes anywhere we
> add those after the import.
> Many thanks for any input !!
>|||We receive product updates in an XML file. We use XMLSpy because the
company we receive the file from does...just so we can skip any
inconsistencies. XMLSpy uses either SQLOLEDB or SQL Native Client
connection and then creates the necessary tables in an empty database. It
then loads all the product rows from the XML file into the respective
tables. 15 minutes was reasonable we think as XMLSpy is doing an enourmous
amount of string processing and then inserting rows from a workstation into
the server.
XMLSpy is also deriving keys from related data that we specify. These keys
are included in the data but they are just integer columns at that point.
We create the actual indexes after the import is done. This is just another
reason for 15 minutes vs 15 seconds.
At any rate, it does boil down to just reading through an XML file and
inserting rows into a table. I'm not wanting to be skimpy on details but
that's about all there is to it. This isn't a procedure I have control of,
it is a 3rd party COM object we call into to do the import so I cannot
address the NOCOUNT issue. I do not believe it to be the 3rd parties issue
either as it worked fine until now. We've tried this on both SQL2k5 32 and
64 bit servers with the same results.
I've double-checked the recovery method is set to simple...compatibility is
90. Even turned off auto create/update statistics. Would it help any to
use bulk recovery mode? We don't need ANY logging of this data it is
completely transient in nature.
Thanks for responding!!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OjwfA7hoGHA.148@.TK2MSFTNGP04.phx.gbl...
> That's not much to go on Tim. I don't know what this XMLSpy does but even
> 15 minutes is way to long a time to import just a few thousand rows. I
> would think 15 seconds would be too long. Can you give some more details
> on exactly what it is doing? Do you have SET NOCOUNT ON in the job step?
> --
> Andrew J. Kelly SQL MVP
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23yy6klgoGHA.5084@.TK2MSFTNGP03.phx.gbl...
>|||Tim Greenwood wrote:
> We have a process that uses XMLSpy to import an xml file of products/prici
ng
> daily. We receive this file through an ftp site. This process usually ra
n
> in 15-20 min with SQL 2000. It is now running about 2 1/2 hours which is
> unacceptable. Any ideas what could be so different?
> The database used is set to compatibility level 90, simple recovery mode,
> ansi defaults. We've tried turning off create and udpate of statistics as
> this is temporary data used for updating other tables. Nothing seems to
> make a difference. I just bet there's something simple I'm missing here..
> There is only about 8 tables, 14k-15k rows each and no indexes anywhere we
> add those after the import.
> Many thanks for any input !!
>
Ignoring the XML aspect for now, start with the basics, look at Perfmon
while this process is running. Look at Avg disk queue lengths, CPU %.
What other databases are hosted on this machine? Is the transaction log
on the same volume as the data file? Where is TEMPDB? Multi-processor
machine?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Is this a new server or OS as well? Is this SQLSpy running ont he same
server as SQL Server? If so are you sure there is enough memory for both?
Have you looked at profiler and perfmon to see what may be going on? You
need to narrow down the possibilities otherwise it's really hard to say.
These may help:
http://www.sql-server-performance.c...nce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pr...perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:OuMtLKioGHA.4024@.TK2MSFTNGP03.phx.gbl...
> We receive product updates in an XML file. We use XMLSpy because the
> company we receive the file from does...just so we can skip any
> inconsistencies. XMLSpy uses either SQLOLEDB or SQL Native Client
> connection and then creates the necessary tables in an empty database. It
> then loads all the product rows from the XML file into the respective
> tables. 15 minutes was reasonable we think as XMLSpy is doing an
> enourmous amount of string processing and then inserting rows from a
> workstation into the server.
> XMLSpy is also deriving keys from related data that we specify. These
> keys are included in the data but they are just integer columns at that
> point. We create the actual indexes after the import is done. This is
> just another reason for 15 minutes vs 15 seconds.
> At any rate, it does boil down to just reading through an XML file and
> inserting rows into a table. I'm not wanting to be skimpy on details but
> that's about all there is to it. This isn't a procedure I have control
> of, it is a 3rd party COM object we call into to do the import so I cannot
> address the NOCOUNT issue. I do not believe it to be the 3rd parties
> issue either as it worked fine until now. We've tried this on both SQL2k5
> 32 and 64 bit servers with the same results.
> I've double-checked the recovery method is set to simple...compatibility
> is 90. Even turned off auto create/update statistics. Would it help any
> to use bulk recovery mode? We don't need ANY logging of this data it is
> completely transient in nature.
> Thanks for responding!!
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OjwfA7hoGHA.148@.TK2MSFTNGP04.phx.gbl...
>|||Yes I will run profiler on monday while this is running. To answer your
questions...we have 7 db's on this server. All tlogs are on their own
mirrored set of spindles and tempdb is on it's own mirrored set as well. It
is a dual 64-bit machine. 64bit sql2005 and 64bit windows server 2003
enterprise.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:OY4gPrioGHA.4036@.TK2MSFTNGP05.phx.gbl...
> Tim Greenwood wrote:
> Ignoring the XML aspect for now, start with the basics, look at Perfmon
> while this process is running. Look at Avg disk queue lengths, CPU %.
> What other databases are hosted on this machine? Is the transaction log
> on the same volume as the data file? Where is TEMPDB? Multi-processor
> machine?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Subscribe to:
Posts (Atom)