Hi!
I have a database wich is about 40GB. One table is very big (and many rows),
about 20GB.
I can see in Sql Profiler that some questions are taking a few minutes to
end and during this time the processors are going 100% (sqlserver.exe).
After I did a maintenance job "Reorganize data and index pages" the same
questions ended in a few seconds only and no processor activity. Now a week
later I have the same problem. I cannot run the maintenance job "online"
because it's causing locks and heavy processor activity.
Can I solve this problem in any other way than "Reorganize data and index
pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
help.
Regards
Johan
Johan
Identify a slow running queries. Get the execution plan of them and see
does the optimizer available to use index or it doesn't
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> Hi!
> I have a database wich is about 40GB. One table is very big (and many
> rows),
> about 20GB.
> I can see in Sql Profiler that some questions are taking a few minutes to
> end and during this time the processors are going 100% (sqlserver.exe).
> After I did a maintenance job "Reorganize data and index pages" the same
> questions ended in a few seconds only and no processor activity. Now a
> week
> later I have the same problem. I cannot run the maintenance job "online"
> because it's causing locks and heavy processor activity.
> Can I solve this problem in any other way than "Reorganize data and index
> pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did
> not
> help.
> --
> Regards
> Johan
|||I have identified a slow running query and run it in the execution plan.
The query (a join query) runs through 2 tables, both have one clustered
index and several non-clustered indexes.
Regards
Johan
"Uri Dimant" wrote:
> Johan
> Identify a slow running queries. Get the execution plan of them and see
> does the optimizer available to use index or it doesn't
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
>
>
|||And the optimizer uses indexes to create an execution plan
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...[vbcol=seagreen]
>I have identified a slow running query and run it in the execution plan.
> The query (a join query) runs through 2 tables, both have one clustered
> index and several non-clustered indexes.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
|||Yes, it goes through 2 Indes Seeks, one for each table.
Regards
Johan
"Uri Dimant" wrote:
> And the optimizer uses indexes to create an execution plan
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
>
>
|||Well, how much data does the query return ?
Perhaps , you need to re-design the indexes to be more efficient
Look at SET STATISTICS IO ON commad. (Logical reads)
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...[vbcol=seagreen]
> Yes, it goes through 2 Indes Seeks, one for each table.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
|||The questions returns no data. 0 rows...
I did get this when I run with SET STATISTICS IO ON:
Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
read-ahead reads 0.
Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
read-ahead reads 0.
Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
read-ahead reads 0.
Regards
Johan
"Uri Dimant" wrote:
> Well, how much data does the query return ?
> Perhaps , you need to re-design the indexes to be more efficient
> Look at SET STATISTICS IO ON commad. (Logical reads)
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
>
>
|||On Wed, 2 Nov 2005 04:26:14 -0800, "JohanSql"
<JohanSql@.discussions.microsoft.com> wrote:
>I have a database wich is about 40GB. One table is very big (and many rows),
>about 20GB.
>I can see in Sql Profiler that some questions are taking a few minutes to
>end and during this time the processors are going 100% (sqlserver.exe).
>After I did a maintenance job "Reorganize data and index pages" the same
>questions ended in a few seconds only and no processor activity. Now a week
>later I have the same problem. I cannot run the maintenance job "online"
>because it's causing locks and heavy processor activity.
>Can I solve this problem in any other way than "Reorganize data and index
>pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
>help.
Seems odd that you go from a few seconds to several minutes.
I guess that many new records are added during the week?
Are many records also deleted?
What is the primary key on the big table?
Can you post the source for the query?
Josh
|||It would be useful to see the query plan, some DDL and the purpose of
the query.
Do you have foreign keys between the tables you are joining?
Does the query use parallelism during execution? What SQL-Server Service
pack are you running?
If the query plan is using loop joins, then it would mean that each
lookup requirs (on average) 24 logical reads. That does not sound
optimal. Index tuning could probably improve that.
Anyway, your post does not contain enough information to make an
educated guess, but just barely enough to make wild guesses.
Gert-Jan
JohanSql wrote:[vbcol=seagreen]
> The questions returns no data. 0 rows...
> I did get this when I run with SET STATISTICS IO ON:
> Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
> read-ahead reads 0.
> Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
> read-ahead reads 0.
> Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
> read-ahead reads 0.
> --
> Regards
> Johan
> "Uri Dimant" wrote:
Showing posts with label 20gb. Show all posts
Showing posts with label 20gb. Show all posts
Monday, March 19, 2012
Bad performance in some questions (100% CPU)
Hi!
I have a database wich is about 40GB. One table is very big (and many rows),
about 20GB.
I can see in Sql Profiler that some questions are taking a few minutes to
end and during this time the processors are going 100% (sqlserver.exe).
After I did a maintenance job "Reorganize data and index pages" the same
questions ended in a few seconds only and no processor activity. Now a week
later I have the same problem. I cannot run the maintenance job "online"
because it's causing locks and heavy processor activity.
Can I solve this problem in any other way than "Reorganize data and index
pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
help.
--
Regards
JohanJohan
Identify a slow running queries. Get the execution plan of them and see
does the optimizer available to use index or it doesn't
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> Hi!
> I have a database wich is about 40GB. One table is very big (and many
> rows),
> about 20GB.
> I can see in Sql Profiler that some questions are taking a few minutes to
> end and during this time the processors are going 100% (sqlserver.exe).
> After I did a maintenance job "Reorganize data and index pages" the same
> questions ended in a few seconds only and no processor activity. Now a
> week
> later I have the same problem. I cannot run the maintenance job "online"
> because it's causing locks and heavy processor activity.
> Can I solve this problem in any other way than "Reorganize data and index
> pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did
> not
> help.
> --
> Regards
> Johan|||I have identified a slow running query and run it in the execution plan.
The query (a join query) runs through 2 tables, both have one clustered
index and several non-clustered indexes.
--
Regards
Johan
"Uri Dimant" wrote:
> Johan
> Identify a slow running queries. Get the execution plan of them and see
> does the optimizer available to use index or it doesn't
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> > Hi!
> > I have a database wich is about 40GB. One table is very big (and many
> > rows),
> > about 20GB.
> > I can see in Sql Profiler that some questions are taking a few minutes to
> > end and during this time the processors are going 100% (sqlserver.exe).
> > After I did a maintenance job "Reorganize data and index pages" the same
> > questions ended in a few seconds only and no processor activity. Now a
> > week
> > later I have the same problem. I cannot run the maintenance job "online"
> > because it's causing locks and heavy processor activity.
> > Can I solve this problem in any other way than "Reorganize data and index
> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did
> > not
> > help.
> > --
> > Regards
> > Johan
>
>|||And the optimizer uses indexes to create an execution plan
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
>I have identified a slow running query and run it in the execution plan.
> The query (a join query) runs through 2 tables, both have one clustered
> index and several non-clustered indexes.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
>> Johan
>> Identify a slow running queries. Get the execution plan of them and see
>> does the optimizer available to use index or it doesn't
>>
>>
>> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
>> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
>> > Hi!
>> > I have a database wich is about 40GB. One table is very big (and many
>> > rows),
>> > about 20GB.
>> > I can see in Sql Profiler that some questions are taking a few minutes
>> > to
>> > end and during this time the processors are going 100% (sqlserver.exe).
>> > After I did a maintenance job "Reorganize data and index pages" the
>> > same
>> > questions ended in a few seconds only and no processor activity. Now a
>> > week
>> > later I have the same problem. I cannot run the maintenance job
>> > "online"
>> > because it's causing locks and heavy processor activity.
>> > Can I solve this problem in any other way than "Reorganize data and
>> > index
>> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
>> > did
>> > not
>> > help.
>> > --
>> > Regards
>> > Johan
>>|||Yes, it goes through 2 Indes Seeks, one for each table.
--
Regards
Johan
"Uri Dimant" wrote:
> And the optimizer uses indexes to create an execution plan
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
> >I have identified a slow running query and run it in the execution plan.
> > The query (a join query) runs through 2 tables, both have one clustered
> > index and several non-clustered indexes.
> > --
> > Regards
> > Johan
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Johan
> >>
> >> Identify a slow running queries. Get the execution plan of them and see
> >> does the optimizer available to use index or it doesn't
> >>
> >>
> >>
> >>
> >>
> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> >> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> >> > Hi!
> >> > I have a database wich is about 40GB. One table is very big (and many
> >> > rows),
> >> > about 20GB.
> >> > I can see in Sql Profiler that some questions are taking a few minutes
> >> > to
> >> > end and during this time the processors are going 100% (sqlserver.exe).
> >> > After I did a maintenance job "Reorganize data and index pages" the
> >> > same
> >> > questions ended in a few seconds only and no processor activity. Now a
> >> > week
> >> > later I have the same problem. I cannot run the maintenance job
> >> > "online"
> >> > because it's causing locks and heavy processor activity.
> >> > Can I solve this problem in any other way than "Reorganize data and
> >> > index
> >> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
> >> > did
> >> > not
> >> > help.
> >> > --
> >> > Regards
> >> > Johan
> >>
> >>
> >>
>
>|||Well, how much data does the query return ?
Perhaps , you need to re-design the indexes to be more efficient
Look at SET STATISTICS IO ON commad. (Logical reads)
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
> Yes, it goes through 2 Indes Seeks, one for each table.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
>> And the optimizer uses indexes to create an execution plan
>> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
>> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
>> >I have identified a slow running query and run it in the execution plan.
>> > The query (a join query) runs through 2 tables, both have one clustered
>> > index and several non-clustered indexes.
>> > --
>> > Regards
>> > Johan
>> >
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Johan
>> >>
>> >> Identify a slow running queries. Get the execution plan of them and
>> >> see
>> >> does the optimizer available to use index or it doesn't
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
>> >> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
>> >> > Hi!
>> >> > I have a database wich is about 40GB. One table is very big (and
>> >> > many
>> >> > rows),
>> >> > about 20GB.
>> >> > I can see in Sql Profiler that some questions are taking a few
>> >> > minutes
>> >> > to
>> >> > end and during this time the processors are going 100%
>> >> > (sqlserver.exe).
>> >> > After I did a maintenance job "Reorganize data and index pages" the
>> >> > same
>> >> > questions ended in a few seconds only and no processor activity. Now
>> >> > a
>> >> > week
>> >> > later I have the same problem. I cannot run the maintenance job
>> >> > "online"
>> >> > because it's causing locks and heavy processor activity.
>> >> > Can I solve this problem in any other way than "Reorganize data and
>> >> > index
>> >> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
>> >> > did
>> >> > not
>> >> > help.
>> >> > --
>> >> > Regards
>> >> > Johan
>> >>
>> >>
>> >>
>>|||The questions returns no data. 0 rows...
I did get this when I run with SET STATISTICS IO ON:
Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
read-ahead reads 0.
Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
read-ahead reads 0.
Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
read-ahead reads 0.
--
Regards
Johan
"Uri Dimant" wrote:
> Well, how much data does the query return ?
> Perhaps , you need to re-design the indexes to be more efficient
> Look at SET STATISTICS IO ON commad. (Logical reads)
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
> > Yes, it goes through 2 Indes Seeks, one for each table.
> > --
> > Regards
> > Johan
> >
> >
> > "Uri Dimant" wrote:
> >
> >> And the optimizer uses indexes to create an execution plan
> >>
> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> >> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
> >> >I have identified a slow running query and run it in the execution plan.
> >> > The query (a join query) runs through 2 tables, both have one clustered
> >> > index and several non-clustered indexes.
> >> > --
> >> > Regards
> >> > Johan
> >> >
> >> >
> >> > "Uri Dimant" wrote:
> >> >
> >> >> Johan
> >> >>
> >> >> Identify a slow running queries. Get the execution plan of them and
> >> >> see
> >> >> does the optimizer available to use index or it doesn't
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> >> >> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> >> >> > Hi!
> >> >> > I have a database wich is about 40GB. One table is very big (and
> >> >> > many
> >> >> > rows),
> >> >> > about 20GB.
> >> >> > I can see in Sql Profiler that some questions are taking a few
> >> >> > minutes
> >> >> > to
> >> >> > end and during this time the processors are going 100%
> >> >> > (sqlserver.exe).
> >> >> > After I did a maintenance job "Reorganize data and index pages" the
> >> >> > same
> >> >> > questions ended in a few seconds only and no processor activity. Now
> >> >> > a
> >> >> > week
> >> >> > later I have the same problem. I cannot run the maintenance job
> >> >> > "online"
> >> >> > because it's causing locks and heavy processor activity.
> >> >> > Can I solve this problem in any other way than "Reorganize data and
> >> >> > index
> >> >> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
> >> >> > did
> >> >> > not
> >> >> > help.
> >> >> > --
> >> >> > Regards
> >> >> > Johan
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||On Wed, 2 Nov 2005 04:26:14 -0800, "JohanSql"
<JohanSql@.discussions.microsoft.com> wrote:
>I have a database wich is about 40GB. One table is very big (and many rows),
>about 20GB.
>I can see in Sql Profiler that some questions are taking a few minutes to
>end and during this time the processors are going 100% (sqlserver.exe).
>After I did a maintenance job "Reorganize data and index pages" the same
>questions ended in a few seconds only and no processor activity. Now a week
>later I have the same problem. I cannot run the maintenance job "online"
>because it's causing locks and heavy processor activity.
>Can I solve this problem in any other way than "Reorganize data and index
>pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
>help.
Seems odd that you go from a few seconds to several minutes.
I guess that many new records are added during the week?
Are many records also deleted?
What is the primary key on the big table?
Can you post the source for the query?
Josh|||It would be useful to see the query plan, some DDL and the purpose of
the query.
Do you have foreign keys between the tables you are joining?
Does the query use parallelism during execution? What SQL-Server Service
pack are you running?
If the query plan is using loop joins, then it would mean that each
lookup requirs (on average) 24 logical reads. That does not sound
optimal. Index tuning could probably improve that.
Anyway, your post does not contain enough information to make an
educated guess, but just barely enough to make wild guesses.
Gert-Jan
JohanSql wrote:
> The questions returns no data. 0 rows...
> I did get this when I run with SET STATISTICS IO ON:
> Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
> read-ahead reads 0.
> Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
> read-ahead reads 0.
> Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
> read-ahead reads 0.
> --
> Regards
> Johan
> "Uri Dimant" wrote:
> > Well, how much data does the query return ?
> >
> > Perhaps , you need to re-design the indexes to be more efficient
> >
> > Look at SET STATISTICS IO ON commad. (Logical reads)
> >
> >
> >
> >
> > "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> > news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
> > > Yes, it goes through 2 Indes Seeks, one for each table.
> > > --
> > > Regards
> > > Johan
> > >
> > >
> > > "Uri Dimant" wrote:
> > >
> > >> And the optimizer uses indexes to create an execution plan
> > >>
> > >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> > >> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
> > >> >I have identified a slow running query and run it in the execution plan.
> > >> > The query (a join query) runs through 2 tables, both have one clustered
> > >> > index and several non-clustered indexes.
> > >> > --
> > >> > Regards
> > >> > Johan
> > >> >
> > >> >
> > >> > "Uri Dimant" wrote:
> > >> >
> > >> >> Johan
> > >> >>
> > >> >> Identify a slow running queries. Get the execution plan of them and
> > >> >> see
> > >> >> does the optimizer available to use index or it doesn't
> > >> >>
> > >> >>
> > >> >>
> > >> >>
> > >> >>
> > >> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> > >> >> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> > >> >> > Hi!
> > >> >> > I have a database wich is about 40GB. One table is very big (and
> > >> >> > many
> > >> >> > rows),
> > >> >> > about 20GB.
> > >> >> > I can see in Sql Profiler that some questions are taking a few
> > >> >> > minutes
> > >> >> > to
> > >> >> > end and during this time the processors are going 100%
> > >> >> > (sqlserver.exe).
> > >> >> > After I did a maintenance job "Reorganize data and index pages" the
> > >> >> > same
> > >> >> > questions ended in a few seconds only and no processor activity. Now
> > >> >> > a
> > >> >> > week
> > >> >> > later I have the same problem. I cannot run the maintenance job
> > >> >> > "online"
> > >> >> > because it's causing locks and heavy processor activity.
> > >> >> > Can I solve this problem in any other way than "Reorganize data and
> > >> >> > index
> > >> >> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
> > >> >> > did
> > >> >> > not
> > >> >> > help.
> > >> >> > --
> > >> >> > Regards
> > >> >> > Johan
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >
I have a database wich is about 40GB. One table is very big (and many rows),
about 20GB.
I can see in Sql Profiler that some questions are taking a few minutes to
end and during this time the processors are going 100% (sqlserver.exe).
After I did a maintenance job "Reorganize data and index pages" the same
questions ended in a few seconds only and no processor activity. Now a week
later I have the same problem. I cannot run the maintenance job "online"
because it's causing locks and heavy processor activity.
Can I solve this problem in any other way than "Reorganize data and index
pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
help.
--
Regards
JohanJohan
Identify a slow running queries. Get the execution plan of them and see
does the optimizer available to use index or it doesn't
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> Hi!
> I have a database wich is about 40GB. One table is very big (and many
> rows),
> about 20GB.
> I can see in Sql Profiler that some questions are taking a few minutes to
> end and during this time the processors are going 100% (sqlserver.exe).
> After I did a maintenance job "Reorganize data and index pages" the same
> questions ended in a few seconds only and no processor activity. Now a
> week
> later I have the same problem. I cannot run the maintenance job "online"
> because it's causing locks and heavy processor activity.
> Can I solve this problem in any other way than "Reorganize data and index
> pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did
> not
> help.
> --
> Regards
> Johan|||I have identified a slow running query and run it in the execution plan.
The query (a join query) runs through 2 tables, both have one clustered
index and several non-clustered indexes.
--
Regards
Johan
"Uri Dimant" wrote:
> Johan
> Identify a slow running queries. Get the execution plan of them and see
> does the optimizer available to use index or it doesn't
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> > Hi!
> > I have a database wich is about 40GB. One table is very big (and many
> > rows),
> > about 20GB.
> > I can see in Sql Profiler that some questions are taking a few minutes to
> > end and during this time the processors are going 100% (sqlserver.exe).
> > After I did a maintenance job "Reorganize data and index pages" the same
> > questions ended in a few seconds only and no processor activity. Now a
> > week
> > later I have the same problem. I cannot run the maintenance job "online"
> > because it's causing locks and heavy processor activity.
> > Can I solve this problem in any other way than "Reorganize data and index
> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did
> > not
> > help.
> > --
> > Regards
> > Johan
>
>|||And the optimizer uses indexes to create an execution plan
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
>I have identified a slow running query and run it in the execution plan.
> The query (a join query) runs through 2 tables, both have one clustered
> index and several non-clustered indexes.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
>> Johan
>> Identify a slow running queries. Get the execution plan of them and see
>> does the optimizer available to use index or it doesn't
>>
>>
>> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
>> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
>> > Hi!
>> > I have a database wich is about 40GB. One table is very big (and many
>> > rows),
>> > about 20GB.
>> > I can see in Sql Profiler that some questions are taking a few minutes
>> > to
>> > end and during this time the processors are going 100% (sqlserver.exe).
>> > After I did a maintenance job "Reorganize data and index pages" the
>> > same
>> > questions ended in a few seconds only and no processor activity. Now a
>> > week
>> > later I have the same problem. I cannot run the maintenance job
>> > "online"
>> > because it's causing locks and heavy processor activity.
>> > Can I solve this problem in any other way than "Reorganize data and
>> > index
>> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
>> > did
>> > not
>> > help.
>> > --
>> > Regards
>> > Johan
>>|||Yes, it goes through 2 Indes Seeks, one for each table.
--
Regards
Johan
"Uri Dimant" wrote:
> And the optimizer uses indexes to create an execution plan
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
> >I have identified a slow running query and run it in the execution plan.
> > The query (a join query) runs through 2 tables, both have one clustered
> > index and several non-clustered indexes.
> > --
> > Regards
> > Johan
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Johan
> >>
> >> Identify a slow running queries. Get the execution plan of them and see
> >> does the optimizer available to use index or it doesn't
> >>
> >>
> >>
> >>
> >>
> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> >> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> >> > Hi!
> >> > I have a database wich is about 40GB. One table is very big (and many
> >> > rows),
> >> > about 20GB.
> >> > I can see in Sql Profiler that some questions are taking a few minutes
> >> > to
> >> > end and during this time the processors are going 100% (sqlserver.exe).
> >> > After I did a maintenance job "Reorganize data and index pages" the
> >> > same
> >> > questions ended in a few seconds only and no processor activity. Now a
> >> > week
> >> > later I have the same problem. I cannot run the maintenance job
> >> > "online"
> >> > because it's causing locks and heavy processor activity.
> >> > Can I solve this problem in any other way than "Reorganize data and
> >> > index
> >> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
> >> > did
> >> > not
> >> > help.
> >> > --
> >> > Regards
> >> > Johan
> >>
> >>
> >>
>
>|||Well, how much data does the query return ?
Perhaps , you need to re-design the indexes to be more efficient
Look at SET STATISTICS IO ON commad. (Logical reads)
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
> Yes, it goes through 2 Indes Seeks, one for each table.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
>> And the optimizer uses indexes to create an execution plan
>> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
>> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
>> >I have identified a slow running query and run it in the execution plan.
>> > The query (a join query) runs through 2 tables, both have one clustered
>> > index and several non-clustered indexes.
>> > --
>> > Regards
>> > Johan
>> >
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Johan
>> >>
>> >> Identify a slow running queries. Get the execution plan of them and
>> >> see
>> >> does the optimizer available to use index or it doesn't
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
>> >> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
>> >> > Hi!
>> >> > I have a database wich is about 40GB. One table is very big (and
>> >> > many
>> >> > rows),
>> >> > about 20GB.
>> >> > I can see in Sql Profiler that some questions are taking a few
>> >> > minutes
>> >> > to
>> >> > end and during this time the processors are going 100%
>> >> > (sqlserver.exe).
>> >> > After I did a maintenance job "Reorganize data and index pages" the
>> >> > same
>> >> > questions ended in a few seconds only and no processor activity. Now
>> >> > a
>> >> > week
>> >> > later I have the same problem. I cannot run the maintenance job
>> >> > "online"
>> >> > because it's causing locks and heavy processor activity.
>> >> > Can I solve this problem in any other way than "Reorganize data and
>> >> > index
>> >> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
>> >> > did
>> >> > not
>> >> > help.
>> >> > --
>> >> > Regards
>> >> > Johan
>> >>
>> >>
>> >>
>>|||The questions returns no data. 0 rows...
I did get this when I run with SET STATISTICS IO ON:
Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
read-ahead reads 0.
Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
read-ahead reads 0.
Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
read-ahead reads 0.
--
Regards
Johan
"Uri Dimant" wrote:
> Well, how much data does the query return ?
> Perhaps , you need to re-design the indexes to be more efficient
> Look at SET STATISTICS IO ON commad. (Logical reads)
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
> > Yes, it goes through 2 Indes Seeks, one for each table.
> > --
> > Regards
> > Johan
> >
> >
> > "Uri Dimant" wrote:
> >
> >> And the optimizer uses indexes to create an execution plan
> >>
> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> >> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
> >> >I have identified a slow running query and run it in the execution plan.
> >> > The query (a join query) runs through 2 tables, both have one clustered
> >> > index and several non-clustered indexes.
> >> > --
> >> > Regards
> >> > Johan
> >> >
> >> >
> >> > "Uri Dimant" wrote:
> >> >
> >> >> Johan
> >> >>
> >> >> Identify a slow running queries. Get the execution plan of them and
> >> >> see
> >> >> does the optimizer available to use index or it doesn't
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> >> >> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> >> >> > Hi!
> >> >> > I have a database wich is about 40GB. One table is very big (and
> >> >> > many
> >> >> > rows),
> >> >> > about 20GB.
> >> >> > I can see in Sql Profiler that some questions are taking a few
> >> >> > minutes
> >> >> > to
> >> >> > end and during this time the processors are going 100%
> >> >> > (sqlserver.exe).
> >> >> > After I did a maintenance job "Reorganize data and index pages" the
> >> >> > same
> >> >> > questions ended in a few seconds only and no processor activity. Now
> >> >> > a
> >> >> > week
> >> >> > later I have the same problem. I cannot run the maintenance job
> >> >> > "online"
> >> >> > because it's causing locks and heavy processor activity.
> >> >> > Can I solve this problem in any other way than "Reorganize data and
> >> >> > index
> >> >> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
> >> >> > did
> >> >> > not
> >> >> > help.
> >> >> > --
> >> >> > Regards
> >> >> > Johan
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||On Wed, 2 Nov 2005 04:26:14 -0800, "JohanSql"
<JohanSql@.discussions.microsoft.com> wrote:
>I have a database wich is about 40GB. One table is very big (and many rows),
>about 20GB.
>I can see in Sql Profiler that some questions are taking a few minutes to
>end and during this time the processors are going 100% (sqlserver.exe).
>After I did a maintenance job "Reorganize data and index pages" the same
>questions ended in a few seconds only and no processor activity. Now a week
>later I have the same problem. I cannot run the maintenance job "online"
>because it's causing locks and heavy processor activity.
>Can I solve this problem in any other way than "Reorganize data and index
>pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
>help.
Seems odd that you go from a few seconds to several minutes.
I guess that many new records are added during the week?
Are many records also deleted?
What is the primary key on the big table?
Can you post the source for the query?
Josh|||It would be useful to see the query plan, some DDL and the purpose of
the query.
Do you have foreign keys between the tables you are joining?
Does the query use parallelism during execution? What SQL-Server Service
pack are you running?
If the query plan is using loop joins, then it would mean that each
lookup requirs (on average) 24 logical reads. That does not sound
optimal. Index tuning could probably improve that.
Anyway, your post does not contain enough information to make an
educated guess, but just barely enough to make wild guesses.
Gert-Jan
JohanSql wrote:
> The questions returns no data. 0 rows...
> I did get this when I run with SET STATISTICS IO ON:
> Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
> read-ahead reads 0.
> Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
> read-ahead reads 0.
> Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
> read-ahead reads 0.
> --
> Regards
> Johan
> "Uri Dimant" wrote:
> > Well, how much data does the query return ?
> >
> > Perhaps , you need to re-design the indexes to be more efficient
> >
> > Look at SET STATISTICS IO ON commad. (Logical reads)
> >
> >
> >
> >
> > "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> > news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
> > > Yes, it goes through 2 Indes Seeks, one for each table.
> > > --
> > > Regards
> > > Johan
> > >
> > >
> > > "Uri Dimant" wrote:
> > >
> > >> And the optimizer uses indexes to create an execution plan
> > >>
> > >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> > >> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
> > >> >I have identified a slow running query and run it in the execution plan.
> > >> > The query (a join query) runs through 2 tables, both have one clustered
> > >> > index and several non-clustered indexes.
> > >> > --
> > >> > Regards
> > >> > Johan
> > >> >
> > >> >
> > >> > "Uri Dimant" wrote:
> > >> >
> > >> >> Johan
> > >> >>
> > >> >> Identify a slow running queries. Get the execution plan of them and
> > >> >> see
> > >> >> does the optimizer available to use index or it doesn't
> > >> >>
> > >> >>
> > >> >>
> > >> >>
> > >> >>
> > >> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> > >> >> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> > >> >> > Hi!
> > >> >> > I have a database wich is about 40GB. One table is very big (and
> > >> >> > many
> > >> >> > rows),
> > >> >> > about 20GB.
> > >> >> > I can see in Sql Profiler that some questions are taking a few
> > >> >> > minutes
> > >> >> > to
> > >> >> > end and during this time the processors are going 100%
> > >> >> > (sqlserver.exe).
> > >> >> > After I did a maintenance job "Reorganize data and index pages" the
> > >> >> > same
> > >> >> > questions ended in a few seconds only and no processor activity. Now
> > >> >> > a
> > >> >> > week
> > >> >> > later I have the same problem. I cannot run the maintenance job
> > >> >> > "online"
> > >> >> > because it's causing locks and heavy processor activity.
> > >> >> > Can I solve this problem in any other way than "Reorganize data and
> > >> >> > index
> > >> >> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
> > >> >> > did
> > >> >> > not
> > >> >> > help.
> > >> >> > --
> > >> >> > Regards
> > >> >> > Johan
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >
Bad performance in some questions (100% CPU)
Hi!
I have a database wich is about 40GB. One table is very big (and many rows),
about 20GB.
I can see in Sql Profiler that some questions are taking a few minutes to
end and during this time the processors are going 100% (sqlserver.exe).
After I did a maintenance job "Reorganize data and index pages" the same
questions ended in a few seconds only and no processor activity. Now a week
later I have the same problem. I cannot run the maintenance job "online"
because it's causing locks and heavy processor activity.
Can I solve this problem in any other way than "Reorganize data and index
pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
help.
--
Regards
JohanJohan
Identify a slow running queries. Get the execution plan of them and see
does the optimizer available to use index or it doesn't
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> Hi!
> I have a database wich is about 40GB. One table is very big (and many
> rows),
> about 20GB.
> I can see in Sql Profiler that some questions are taking a few minutes to
> end and during this time the processors are going 100% (sqlserver.exe).
> After I did a maintenance job "Reorganize data and index pages" the same
> questions ended in a few seconds only and no processor activity. Now a
> week
> later I have the same problem. I cannot run the maintenance job "online"
> because it's causing locks and heavy processor activity.
> Can I solve this problem in any other way than "Reorganize data and index
> pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did
> not
> help.
> --
> Regards
> Johan|||I have identified a slow running query and run it in the execution plan.
The query (a join query) runs through 2 tables, both have one clustered
index and several non-clustered indexes.
--
Regards
Johan
"Uri Dimant" wrote:
> Johan
> Identify a slow running queries. Get the execution plan of them and see
> does the optimizer available to use index or it doesn't
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
>
>|||And the optimizer uses indexes to create an execution plan
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...[vbcol=seagreen]
>I have identified a slow running query and run it in the execution plan.
> The query (a join query) runs through 2 tables, both have one clustered
> index and several non-clustered indexes.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
>|||Yes, it goes through 2 Indes Seeks, one for each table.
--
Regards
Johan
"Uri Dimant" wrote:
> And the optimizer uses indexes to create an execution plan
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
>
>|||Well, how much data does the query return ?
Perhaps , you need to re-design the indexes to be more efficient
Look at SET STATISTICS IO ON commad. (Logical reads)
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...[vbcol=seagreen]
> Yes, it goes through 2 Indes Seeks, one for each table.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
>|||The questions returns no data. 0 rows...
I did get this when I run with SET STATISTICS IO ON:
Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
read-ahead reads 0.
Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
read-ahead reads 0.
Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
read-ahead reads 0.
--
Regards
Johan
"Uri Dimant" wrote:
> Well, how much data does the query return ?
> Perhaps , you need to re-design the indexes to be more efficient
> Look at SET STATISTICS IO ON commad. (Logical reads)
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
>
>|||On Wed, 2 Nov 2005 04:26:14 -0800, "JohanSql"
<JohanSql@.discussions.microsoft.com> wrote:
>I have a database wich is about 40GB. One table is very big (and many rows)
,
>about 20GB.
>I can see in Sql Profiler that some questions are taking a few minutes to
>end and during this time the processors are going 100% (sqlserver.exe).
>After I did a maintenance job "Reorganize data and index pages" the same
>questions ended in a few seconds only and no processor activity. Now a week
>later I have the same problem. I cannot run the maintenance job "online"
>because it's causing locks and heavy processor activity.
>Can I solve this problem in any other way than "Reorganize data and index
>pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did no
t
>help.
Seems odd that you go from a few seconds to several minutes.
I guess that many new records are added during the week?
Are many records also deleted?
What is the primary key on the big table?
Can you post the source for the query?
Josh|||It would be useful to see the query plan, some DDL and the purpose of
the query.
Do you have foreign keys between the tables you are joining?
Does the query use parallelism during execution? What SQL-Server Service
pack are you running?
If the query plan is using loop joins, then it would mean that each
lookup requirs (on average) 24 logical reads. That does not sound
optimal. Index tuning could probably improve that.
Anyway, your post does not contain enough information to make an
educated guess, but just barely enough to make wild guesses.
Gert-Jan
JohanSql wrote:[vbcol=seagreen]
> The questions returns no data. 0 rows...
> I did get this when I run with SET STATISTICS IO ON:
> Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0
,
> read-ahead reads 0.
> Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
> read-ahead reads 0.
> Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads
0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads
0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads
0,
> read-ahead reads 0.
> --
> Regards
> Johan
> "Uri Dimant" wrote:
>
I have a database wich is about 40GB. One table is very big (and many rows),
about 20GB.
I can see in Sql Profiler that some questions are taking a few minutes to
end and during this time the processors are going 100% (sqlserver.exe).
After I did a maintenance job "Reorganize data and index pages" the same
questions ended in a few seconds only and no processor activity. Now a week
later I have the same problem. I cannot run the maintenance job "online"
because it's causing locks and heavy processor activity.
Can I solve this problem in any other way than "Reorganize data and index
pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
help.
--
Regards
JohanJohan
Identify a slow running queries. Get the execution plan of them and see
does the optimizer available to use index or it doesn't
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> Hi!
> I have a database wich is about 40GB. One table is very big (and many
> rows),
> about 20GB.
> I can see in Sql Profiler that some questions are taking a few minutes to
> end and during this time the processors are going 100% (sqlserver.exe).
> After I did a maintenance job "Reorganize data and index pages" the same
> questions ended in a few seconds only and no processor activity. Now a
> week
> later I have the same problem. I cannot run the maintenance job "online"
> because it's causing locks and heavy processor activity.
> Can I solve this problem in any other way than "Reorganize data and index
> pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did
> not
> help.
> --
> Regards
> Johan|||I have identified a slow running query and run it in the execution plan.
The query (a join query) runs through 2 tables, both have one clustered
index and several non-clustered indexes.
--
Regards
Johan
"Uri Dimant" wrote:
> Johan
> Identify a slow running queries. Get the execution plan of them and see
> does the optimizer available to use index or it doesn't
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
>
>|||And the optimizer uses indexes to create an execution plan
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...[vbcol=seagreen]
>I have identified a slow running query and run it in the execution plan.
> The query (a join query) runs through 2 tables, both have one clustered
> index and several non-clustered indexes.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
>|||Yes, it goes through 2 Indes Seeks, one for each table.
--
Regards
Johan
"Uri Dimant" wrote:
> And the optimizer uses indexes to create an execution plan
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
>
>|||Well, how much data does the query return ?
Perhaps , you need to re-design the indexes to be more efficient
Look at SET STATISTICS IO ON commad. (Logical reads)
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...[vbcol=seagreen]
> Yes, it goes through 2 Indes Seeks, one for each table.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
>|||The questions returns no data. 0 rows...
I did get this when I run with SET STATISTICS IO ON:
Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
read-ahead reads 0.
Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
read-ahead reads 0.
Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
read-ahead reads 0.
--
Regards
Johan
"Uri Dimant" wrote:
> Well, how much data does the query return ?
> Perhaps , you need to re-design the indexes to be more efficient
> Look at SET STATISTICS IO ON commad. (Logical reads)
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
>
>|||On Wed, 2 Nov 2005 04:26:14 -0800, "JohanSql"
<JohanSql@.discussions.microsoft.com> wrote:
>I have a database wich is about 40GB. One table is very big (and many rows)
,
>about 20GB.
>I can see in Sql Profiler that some questions are taking a few minutes to
>end and during this time the processors are going 100% (sqlserver.exe).
>After I did a maintenance job "Reorganize data and index pages" the same
>questions ended in a few seconds only and no processor activity. Now a week
>later I have the same problem. I cannot run the maintenance job "online"
>because it's causing locks and heavy processor activity.
>Can I solve this problem in any other way than "Reorganize data and index
>pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did no
t
>help.
Seems odd that you go from a few seconds to several minutes.
I guess that many new records are added during the week?
Are many records also deleted?
What is the primary key on the big table?
Can you post the source for the query?
Josh|||It would be useful to see the query plan, some DDL and the purpose of
the query.
Do you have foreign keys between the tables you are joining?
Does the query use parallelism during execution? What SQL-Server Service
pack are you running?
If the query plan is using loop joins, then it would mean that each
lookup requirs (on average) 24 logical reads. That does not sound
optimal. Index tuning could probably improve that.
Anyway, your post does not contain enough information to make an
educated guess, but just barely enough to make wild guesses.
Gert-Jan
JohanSql wrote:[vbcol=seagreen]
> The questions returns no data. 0 rows...
> I did get this when I run with SET STATISTICS IO ON:
> Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0
,
> read-ahead reads 0.
> Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
> read-ahead reads 0.
> Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads
0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads
0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads
0,
> read-ahead reads 0.
> --
> Regards
> Johan
> "Uri Dimant" wrote:
>
Sunday, February 19, 2012
Backup/restore rids free space?
Can someone explain this? I'm trying to do a backup and
restore to get rid of the 20GB of free space.
In EM, Databases/Space Allocated..
Used Free Total
10GB 20GB 30GB
If I backup the DB to tape that has a 15GB capacity, it
backs up just fine, i.e., the free space isn't being
backed up or else it wouldn't fit on the tape.
But, if I restore the DB from tape, it restores 30GB.
Not sure I understand how the free space is being treated
by the backup/restore.
Thanks,
Don
SQL 7.0/NT 4.0
Hi,
I think Transaction log file will be grown high.
How to check the size:-
DBCC SQLPERF(LOGSPACE)
If the transaction file size is huge then use the below commands to backup
the transaction log and shrink the file:-
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
DBCC SHRINKFILE('LDF_FILE_NAME',truncateonly)
Thanks
Hari
MCDBA
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
|||Don,
Backup makes some optimization decisions on what it writes to backup. It
does not need to write all the empty space, eating up tape and time, but it
(as I understand it) is recording enough information to rebuild the
freespace. Think of it as an easy compression algorithm.
However, backup / restore will not change the definition of the database and
the backup knows that it is restoring 30 GB.
Hari gives you guidance on shrinking the log file. The data file can also
be shrunk using DBCC SHRINKFILE if it has become bloated.
Russell Fields
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
|||You might want to look here
(http://www.karaszi.com/SQLServer/info_dont_shrink.asp) first for some
caveats when shrinking data and log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
restore to get rid of the 20GB of free space.
In EM, Databases/Space Allocated..
Used Free Total
10GB 20GB 30GB
If I backup the DB to tape that has a 15GB capacity, it
backs up just fine, i.e., the free space isn't being
backed up or else it wouldn't fit on the tape.
But, if I restore the DB from tape, it restores 30GB.
Not sure I understand how the free space is being treated
by the backup/restore.
Thanks,
Don
SQL 7.0/NT 4.0
Hi,
I think Transaction log file will be grown high.
How to check the size:-
DBCC SQLPERF(LOGSPACE)
If the transaction file size is huge then use the below commands to backup
the transaction log and shrink the file:-
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
DBCC SHRINKFILE('LDF_FILE_NAME',truncateonly)
Thanks
Hari
MCDBA
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
|||Don,
Backup makes some optimization decisions on what it writes to backup. It
does not need to write all the empty space, eating up tape and time, but it
(as I understand it) is recording enough information to rebuild the
freespace. Think of it as an easy compression algorithm.
However, backup / restore will not change the definition of the database and
the backup knows that it is restoring 30 GB.
Hari gives you guidance on shrinking the log file. The data file can also
be shrunk using DBCC SHRINKFILE if it has become bloated.
Russell Fields
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
|||You might want to look here
(http://www.karaszi.com/SQLServer/info_dont_shrink.asp) first for some
caveats when shrinking data and log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
Backup/restore rids free space?
Can someone explain this? I'm trying to do a backup and
restore to get rid of the 20GB of free space.
In EM, Databases/Space Allocated..
Used Free Total
10GB 20GB 30GB
If I backup the DB to tape that has a 15GB capacity, it
backs up just fine, i.e., the free space isn't being
backed up or else it wouldn't fit on the tape.
But, if I restore the DB from tape, it restores 30GB.
Not sure I understand how the free space is being treated
by the backup/restore.
Thanks,
Don
SQL 7.0/NT 4.0Hi,
I think Transaction log file will be grown high.
How to check the size:-
DBCC SQLPERF(LOGSPACE)
If the transaction file size is huge then use the below commands to backup
the transaction log and shrink the file:-
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
DBCC SHRINKFILE('LDF_FILE_NAME',truncateonly)
Thanks
Hari
MCDBA
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx
.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||Don,
Backup makes some optimization decisions on what it writes to backup. It
does not need to write all the empty space, eating up tape and time, but it
(as I understand it) is recording enough information to rebuild the
freespace. Think of it as an easy compression algorithm.
However, backup / restore will not change the definition of the database and
the backup knows that it is restoring 30 GB.
Hari gives you guidance on shrinking the log file. The data file can also
be shrunk using DBCC SHRINKFILE if it has become bloated.
Russell Fields
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx
.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||You might want to look here
(http://www.karaszi.com/SQLServer/info_dont_shrink.asp) first for some
caveats when shrinking data and log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx
.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
restore to get rid of the 20GB of free space.
In EM, Databases/Space Allocated..
Used Free Total
10GB 20GB 30GB
If I backup the DB to tape that has a 15GB capacity, it
backs up just fine, i.e., the free space isn't being
backed up or else it wouldn't fit on the tape.
But, if I restore the DB from tape, it restores 30GB.
Not sure I understand how the free space is being treated
by the backup/restore.
Thanks,
Don
SQL 7.0/NT 4.0Hi,
I think Transaction log file will be grown high.
How to check the size:-
DBCC SQLPERF(LOGSPACE)
If the transaction file size is huge then use the below commands to backup
the transaction log and shrink the file:-
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
DBCC SHRINKFILE('LDF_FILE_NAME',truncateonly)
Thanks
Hari
MCDBA
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx
.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||Don,
Backup makes some optimization decisions on what it writes to backup. It
does not need to write all the empty space, eating up tape and time, but it
(as I understand it) is recording enough information to rebuild the
freespace. Think of it as an easy compression algorithm.
However, backup / restore will not change the definition of the database and
the backup knows that it is restoring 30 GB.
Hari gives you guidance on shrinking the log file. The data file can also
be shrunk using DBCC SHRINKFILE if it has become bloated.
Russell Fields
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx
.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||You might want to look here
(http://www.karaszi.com/SQLServer/info_dont_shrink.asp) first for some
caveats when shrinking data and log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx
.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
Backup/restore rids free space?
Can someone explain this? I'm trying to do a backup and
restore to get rid of the 20GB of free space.
In EM, Databases/Space Allocated..
Used Free Total
10GB 20GB 30GB
If I backup the DB to tape that has a 15GB capacity, it
backs up just fine, i.e., the free space isn't being
backed up or else it wouldn't fit on the tape.
But, if I restore the DB from tape, it restores 30GB.
Not sure I understand how the free space is being treated
by the backup/restore.
Thanks,
Don
SQL 7.0/NT 4.0Hi,
I think Transaction log file will be grown high.
How to check the size:-
DBCC SQLPERF(LOGSPACE)
If the transaction file size is huge then use the below commands to backup
the transaction log and shrink the file:-
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
DBCC SHRINKFILE('LDF_FILE_NAME',truncateonly)
Thanks
Hari
MCDBA
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||Don,
Backup makes some optimization decisions on what it writes to backup. It
does not need to write all the empty space, eating up tape and time, but it
(as I understand it) is recording enough information to rebuild the
freespace. Think of it as an easy compression algorithm.
However, backup / restore will not change the definition of the database and
the backup knows that it is restoring 30 GB.
Hari gives you guidance on shrinking the log file. The data file can also
be shrunk using DBCC SHRINKFILE if it has become bloated.
Russell Fields
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||You might want to look here
(http://www.karaszi.com/SQLServer/info_dont_shrink.asp) first for some
caveats when shrinking data and log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
restore to get rid of the 20GB of free space.
In EM, Databases/Space Allocated..
Used Free Total
10GB 20GB 30GB
If I backup the DB to tape that has a 15GB capacity, it
backs up just fine, i.e., the free space isn't being
backed up or else it wouldn't fit on the tape.
But, if I restore the DB from tape, it restores 30GB.
Not sure I understand how the free space is being treated
by the backup/restore.
Thanks,
Don
SQL 7.0/NT 4.0Hi,
I think Transaction log file will be grown high.
How to check the size:-
DBCC SQLPERF(LOGSPACE)
If the transaction file size is huge then use the below commands to backup
the transaction log and shrink the file:-
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
DBCC SHRINKFILE('LDF_FILE_NAME',truncateonly)
Thanks
Hari
MCDBA
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||Don,
Backup makes some optimization decisions on what it writes to backup. It
does not need to write all the empty space, eating up tape and time, but it
(as I understand it) is recording enough information to rebuild the
freespace. Think of it as an easy compression algorithm.
However, backup / restore will not change the definition of the database and
the backup knows that it is restoring 30 GB.
Hari gives you guidance on shrinking the log file. The data file can also
be shrunk using DBCC SHRINKFILE if it has become bloated.
Russell Fields
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||You might want to look here
(http://www.karaszi.com/SQLServer/info_dont_shrink.asp) first for some
caveats when shrinking data and log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
Thursday, February 16, 2012
backup/recovery strategy
Currently we have about 60 SQL Servers all shape and size (the biggest is
payroll dbs 20gb). I would like very much to standartize backup and recovery
procedure for all servers and dbs. Pls, fellows, help with advice for
procedures, 3rd party tools, backup devices and so on...
I will be grateful if you share your experience...
Hi,
See the below URL's
http://www.microsoft.com/technet/pro...ackuprest.mspx
http://www1.us.dell.com/content/topi...555&l=en&s=biz
http://vyaskn.tripod.com/sql_server_..._practices.htm
Thanks
Hari
SQL Server MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8F912D14-F11A-4693-89D2-23B50213ED02@.microsoft.com...
> Currently we have about 60 SQL Servers all shape and size (the biggest is
> payroll dbs 20gb). I would like very much to standartize backup and
> recovery
> procedure for all servers and dbs. Pls, fellows, help with advice for
> procedures, 3rd party tools, backup devices and so on...
> I will be grateful if you share your experience...
payroll dbs 20gb). I would like very much to standartize backup and recovery
procedure for all servers and dbs. Pls, fellows, help with advice for
procedures, 3rd party tools, backup devices and so on...
I will be grateful if you share your experience...
Hi,
See the below URL's
http://www.microsoft.com/technet/pro...ackuprest.mspx
http://www1.us.dell.com/content/topi...555&l=en&s=biz
http://vyaskn.tripod.com/sql_server_..._practices.htm
Thanks
Hari
SQL Server MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8F912D14-F11A-4693-89D2-23B50213ED02@.microsoft.com...
> Currently we have about 60 SQL Servers all shape and size (the biggest is
> payroll dbs 20gb). I would like very much to standartize backup and
> recovery
> procedure for all servers and dbs. Pls, fellows, help with advice for
> procedures, 3rd party tools, backup devices and so on...
> I will be grateful if you share your experience...
backup/recovery strategy
Currently we have about 60 SQL Servers all shape and size (the biggest is
payroll dbs 20gb). I would like very much to standartize backup and recovery
procedure for all servers and dbs. Pls, fellows, help with advice for
procedures, 3rd party tools, backup devices and so on...
I will be grateful if you share your experience...Hi,
See the below URL's
http://www.microsoft.com/technet/pr...
555&l=en&s=biz
http://vyaskn.tripod.com/ sql_serve...ices
.htm
Thanks
Hari
SQL Server MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8F912D14-F11A-4693-89D2-23B50213ED02@.microsoft.com...
> Currently we have about 60 SQL Servers all shape and size (the biggest is
> payroll dbs 20gb). I would like very much to standartize backup and
> recovery
> procedure for all servers and dbs. Pls, fellows, help with advice for
> procedures, 3rd party tools, backup devices and so on...
> I will be grateful if you share your experience...
payroll dbs 20gb). I would like very much to standartize backup and recovery
procedure for all servers and dbs. Pls, fellows, help with advice for
procedures, 3rd party tools, backup devices and so on...
I will be grateful if you share your experience...Hi,
See the below URL's
http://www.microsoft.com/technet/pr...
555&l=en&s=biz
http://vyaskn.tripod.com/ sql_serve...ices
.htm
Thanks
Hari
SQL Server MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8F912D14-F11A-4693-89D2-23B50213ED02@.microsoft.com...
> Currently we have about 60 SQL Servers all shape and size (the biggest is
> payroll dbs 20gb). I would like very much to standartize backup and
> recovery
> procedure for all servers and dbs. Pls, fellows, help with advice for
> procedures, 3rd party tools, backup devices and so on...
> I will be grateful if you share your experience...
backup/recovery strategy
Currently we have about 60 SQL Servers all shape and size (the biggest is
payroll dbs 20gb). I would like very much to standartize backup and recovery
procedure for all servers and dbs. Pls, fellows, help with advice for
procedures, 3rd party tools, backup devices and so on...
I will be grateful if you share your experience...Hi,
See the below URL's
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx
http://www1.us.dell.com/content/topics/global.aspx/power/en/ps4q00_martin?c=us&cs=555&l=en&s=biz
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm
Thanks
Hari
SQL Server MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8F912D14-F11A-4693-89D2-23B50213ED02@.microsoft.com...
> Currently we have about 60 SQL Servers all shape and size (the biggest is
> payroll dbs 20gb). I would like very much to standartize backup and
> recovery
> procedure for all servers and dbs. Pls, fellows, help with advice for
> procedures, 3rd party tools, backup devices and so on...
> I will be grateful if you share your experience...
payroll dbs 20gb). I would like very much to standartize backup and recovery
procedure for all servers and dbs. Pls, fellows, help with advice for
procedures, 3rd party tools, backup devices and so on...
I will be grateful if you share your experience...Hi,
See the below URL's
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx
http://www1.us.dell.com/content/topics/global.aspx/power/en/ps4q00_martin?c=us&cs=555&l=en&s=biz
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm
Thanks
Hari
SQL Server MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8F912D14-F11A-4693-89D2-23B50213ED02@.microsoft.com...
> Currently we have about 60 SQL Servers all shape and size (the biggest is
> payroll dbs 20gb). I would like very much to standartize backup and
> recovery
> procedure for all servers and dbs. Pls, fellows, help with advice for
> procedures, 3rd party tools, backup devices and so on...
> I will be grateful if you share your experience...
Subscribe to:
Posts (Atom)