Hi All,
I was running the following Query
SELECT s.r_object_id, s.object_name, s.i_chronicle_id, c.r_object_id as content_id, c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label, f.r_folder_path FROM dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f, dm_sysobject_r sr2 WHERE (f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/accelera.com/contact/%') AND sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id AND sr.r_object_id = s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label = 'Active' AND (s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT EXISTS (SELECT r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_chronicle_id AND w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w.r_folder_path = f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_format = 'html' AND w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full_format = 'zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and ( language_code='en_US' or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.parent_id from dm_relation r where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATION_OF' and r.child_id in (select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 where sys1.language_code in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_label = 'Active' ) ))) ORDER BY s.i_chronicle_id,s.r_object_id
And the Error: "A database error has occurred during the creation of a cursor (' STATE=01000, CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key length is 900 bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For some combination of large values, the insert/update operation will fail.
Can Any one Help to fix this SQL Enterprise Manager?You could try adding the ROBUST PLAN hint to your query. Read more about it in Books Online.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Raj" <anonymous@.discussions.microsoft.com> wrote in message
news:65B68D28-7D07-422A-9938-5E4C5E504FA3@.microsoft.com...
> Hi All,
> I was running the following Query
> SELECT s.r_object_id, s.object_name, s.i_chronicle_id, c.r_object_id as content_id,
c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label, f.r_folder_path FROM
dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f, dm_sysobject_r sr2 WHERE
(f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/accelera.com/contact/%') AND
sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id AND sr.r_object_id =s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label = 'Active' AND
(s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT EXISTS (SELECT
r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_chronicle_id AND
w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w.r_folder_path =f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_format = 'html' AND
w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full_format ='zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and ( language_code='en_US'
or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.parent_id from dm_relation r
where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATION_OF' and r.child_id in
(select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 where sys1.language_code
in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_label = 'Active' ) )))
ORDER BY s.i_chronicle_id,s.r_object_id
> And the Error: "A database error has occurred during the creation of a cursor (' STATE=01000,
CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key length is 900
bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For some combination of large
values, the insert/update operation will fail.
> Can Any one Help to fix this SQL Enterprise Manager?
>|||Perhaps the index indeed is larger that 900 bytes for some rows. This is not configurable. Work with
the vendor of the app and see what can be done to handle this. One possible thing is, of course to
remove the index...
I still suggest you read about what the ROBUST PLAN does. It is likely that the intermediate steps
of the query processing creates some work table for which the row length exceeds the max for SQL
Server.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Raj" <anonymous@.discussions.microsoft.com> wrote in message
news:C6E27085-F620-4B28-8F43-0AD14BD9714B@.microsoft.com...
> Hi,
> The Query is generated by Documentum. As you know Documentum is a middleware that generates a
DQL Query (SQL like Query) on the underlying database. As per the error description, it looks like,
I have some Server configuration problem like
> 1) Maximum length of the index RowSetWorkTableSS = 1020 bytes
> 2) Maximum key length = 900 bytes
> what do these signify? How to increase these from EM?
> -- Tibor Karaszi wrote: --
> You could try adding the ROBUST PLAN hint to your query. Read more about it in Books Online.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Raj" <anonymous@.discussions.microsoft.com> wrote in message
> news:65B68D28-7D07-422A-9938-5E4C5E504FA3@.microsoft.com...
> > Hi All,
> >> I was running the following Query
> >> SELECT s.r_object_id, s.object_name, s.i_chronicle_id, c.r_object_id as content_id,
> c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label, f.r_folder_path
FROM
> dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f, dm_sysobject_r sr2 WHERE
> (f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/accelera.com/contact/%')
AND
> sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id AND sr.r_object_id => s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label = 'Active' AND
> (s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT EXISTS (SELECT
> r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_chronicle_id AND
> w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w.r_folder_path => f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_format = 'html' AND
> w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full_format => 'zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and (
language_code='en_US'
> or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.parent_id from
dm_relation r
> where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATION_OF' and r.child_id in
> (select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 where
sys1.language_code
> in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_label ='Active' ) )))
> ORDER BY s.i_chronicle_id,s.r_object_id
> >> And the Error: "A database error has occurred during the creation of a cursor ('
STATE=01000,
> CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key length
is 900
> bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For some combination
of large
> values, the insert/update operation will fail.
> >> Can Any one Help to fix this SQL Enterprise Manager?
> >
Showing posts with label configuration. Show all posts
Showing posts with label configuration. Show all posts
Friday, March 30, 2012
Query Problems - Server Configuration?
Hi All,
I was running the following Query
SELECT s.r_object_id, s.object_name, s.i_chronicle_id, c.r_object_id as cont
ent_id, c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_versio
n_label, f.r_folder_path FROM dm_sysobject_s s, dmr_content_r c, dm_sysobje
ct_r sr, dm_folder_r f
, dm_sysobject_r sr2 WHERE (f.r_folder_path = '/accelera.com/contact' OR f.r
_folder_path like '/accelera.com/contact/%') AND sr2.r_object_id = s.r_objec
t_id AND sr2.i_folder_id = f.r_object_id AND sr.r_object_id = s.r_object_id
AND c.parent_id = s.r_objec
t_id AND sr.r_version_label = 'Active' AND (s.r_modify_date >= DATE('2004-0
2-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT EXISTS (SELECT r_object_id FROM
dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_chronicle_id AND w
.r_object_id = s.r_object_i
d AND w.object_name = s.object_name AND w.r_folder_path = f.r_folder_path AN
D (w.i_full_format = c.i_full_format OR (c.i_full_format = 'html' AND w.i_fu
ll_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full_forma
t = 'zip_pub_html' ) ) ))
and s.a_is_hidden = false and s.a_archive = false and ( language_code='en_US
' or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.pa
rent_id from dm_relation r where r.parent_id = s.r_object_id and r.relation_
name = 'DM_TRANSLATION_OF'
and r.child_id in (select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_s
ysobject_r sys2 where sys1.language_code in ('en_US',' ') and sys1.r_object_
id = sys2.r_object_id and sys2.r_version_label = 'Active' ) ))) ORDER BY s.i
_chronicle_id,s.r_object_id
And the Error: "A database error has occurred during the creation of a cursor (' STATE
=01000, CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maxi
mum key length is 900 bytes. The index 'RowsetWorkTableSS' has maximum lengt
h
of 1020 bytes. For some combination of large values, the insert/update opera
tion will fail.
Can Any one Help to fix this SQL Enterprise Manager?You could try adding the ROBUST PLAN hint to your query. Read more about it
in Books Online.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Raj" <anonymous@.discussions.microsoft.com> wrote in message
news:65B68D28-7D07-422A-9938-5E4C5E504FA3@.microsoft.com...
c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label,
f.r_folder_path FROM
dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f, dm_syso
bject_r sr2 WHERE
(f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/acceler
a.com/contact/%') AND
sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id AND sr.r
_object_id =
s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label = 'Act
ive' AND
(s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT
EXISTS (SELECT
r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_ch
ronicle_id AND
w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w.r_fold
er_path =
f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_format =
'html' AND
w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full
_format =
'zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and
( language_code='en_US'
or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.pare
nt_id from dm_relation r
where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATION_OF'
and r.child_id in
(select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 wh
ere sys1.language_code
in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_
label = 'Active' ) )))
ORDER BY s.i_chronicle_id,s.r_object_id
CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key
length is 900
bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For s
ome combination of large
values, the insert/update operation will fail.
The Query is generated by Documentum. As you know Documentum is a middleware
that generates a DQL Query (SQL like Query) on the underlying database. As
per the error description, it looks like, I have some Server configuration p
roblem like
1) Maximum length of the index RowSetWorkTableSS = 1020 bytes
2) Maximum key length = 900 bytes
what do these signify? How to increase these from EM?
-- Tibor Karaszi wrote: --
You could try adding the ROBUST PLAN hint to your query. Read more about it
in Books Online.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Raj" <anonymous@.discussions.microsoft.com> wrote in message
news:65B68D28-7D07-422A-9938-5E4C5E504FA3@.microsoft.com...
configurable. Work with
the vendor of the app and see what can be done to handle this. One possible
thing is, of course to
remove the index...
I still suggest you read about what the ROBUST PLAN does. It is likely that
the intermediate steps
of the query processing creates some work table for which the row length exc
eeds the max for SQL
Server.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Raj" <anonymous@.discussions.microsoft.com> wrote in message
news:C6E27085-F620-4B28-8F43-0AD14BD9714B@.microsoft.com...
DQL Query (SQL like Query) on the underlying database. As per the error desc
ription, it looks like,
I have some Server configuration problem like
FROM
AND
language_code='en_US'
dm_relation r
sys1.language_code
'Active' ) )))
is 900
of large[QUOTE]
> values, the insert/update operation will fail.
I was running the following Query
SELECT s.r_object_id, s.object_name, s.i_chronicle_id, c.r_object_id as cont
ent_id, c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_versio
n_label, f.r_folder_path FROM dm_sysobject_s s, dmr_content_r c, dm_sysobje
ct_r sr, dm_folder_r f
, dm_sysobject_r sr2 WHERE (f.r_folder_path = '/accelera.com/contact' OR f.r
_folder_path like '/accelera.com/contact/%') AND sr2.r_object_id = s.r_objec
t_id AND sr2.i_folder_id = f.r_object_id AND sr.r_object_id = s.r_object_id
AND c.parent_id = s.r_objec
t_id AND sr.r_version_label = 'Active' AND (s.r_modify_date >= DATE('2004-0
2-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT EXISTS (SELECT r_object_id FROM
dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_chronicle_id AND w
.r_object_id = s.r_object_i
d AND w.object_name = s.object_name AND w.r_folder_path = f.r_folder_path AN
D (w.i_full_format = c.i_full_format OR (c.i_full_format = 'html' AND w.i_fu
ll_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full_forma
t = 'zip_pub_html' ) ) ))
and s.a_is_hidden = false and s.a_archive = false and ( language_code='en_US
' or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.pa
rent_id from dm_relation r where r.parent_id = s.r_object_id and r.relation_
name = 'DM_TRANSLATION_OF'
and r.child_id in (select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_s
ysobject_r sys2 where sys1.language_code in ('en_US',' ') and sys1.r_object_
id = sys2.r_object_id and sys2.r_version_label = 'Active' ) ))) ORDER BY s.i
_chronicle_id,s.r_object_id
And the Error: "A database error has occurred during the creation of a cursor (' STATE
=01000, CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maxi
mum key length is 900 bytes. The index 'RowsetWorkTableSS' has maximum lengt
h
of 1020 bytes. For some combination of large values, the insert/update opera
tion will fail.
Can Any one Help to fix this SQL Enterprise Manager?You could try adding the ROBUST PLAN hint to your query. Read more about it
in Books Online.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Raj" <anonymous@.discussions.microsoft.com> wrote in message
news:65B68D28-7D07-422A-9938-5E4C5E504FA3@.microsoft.com...
quote:
> Hi All,
> I was running the following Query
> SELECT s.r_object_id, s.object_name, s.i_chronicle_id, c.r_object_id as content_id,[/
color]
c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label,
f.r_folder_path FROM
dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f, dm_syso
bject_r sr2 WHERE
(f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/acceler
a.com/contact/%') AND
sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id AND sr.r
_object_id =
s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label = 'Act
ive' AND
(s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT
EXISTS (SELECT
r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_ch
ronicle_id AND
w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w.r_fold
er_path =
f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_format =
'html' AND
w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full
_format =
'zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and
( language_code='en_US'
or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.pare
nt_id from dm_relation r
where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATION_OF'
and r.child_id in
(select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 wh
ere sys1.language_code
in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_
label = 'Active' ) )))
ORDER BY s.i_chronicle_id,s.r_object_id
quote:
> And the Error: "A database error has occurred during the creation of a cursor (' STAT
E=01000,
CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key
length is 900
bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For s
ome combination of large
values, the insert/update operation will fail.
quote:|||Hi,
> Can Any one Help to fix this SQL Enterprise Manager?
>
The Query is generated by Documentum. As you know Documentum is a middleware
that generates a DQL Query (SQL like Query) on the underlying database. As
per the error description, it looks like, I have some Server configuration p
roblem like
1) Maximum length of the index RowSetWorkTableSS = 1020 bytes
2) Maximum key length = 900 bytes
what do these signify? How to increase these from EM?
-- Tibor Karaszi wrote: --
You could try adding the ROBUST PLAN hint to your query. Read more about it
in Books Online.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Raj" <anonymous@.discussions.microsoft.com> wrote in message
news:65B68D28-7D07-422A-9938-5E4C5E504FA3@.microsoft.com...
quote:|||Perhaps the index indeed is larger that 900 bytes for some rows. This is not
> Hi All,
c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label,
f.r_folder_path FROM
dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f, dm_syso
bject_r sr2 WHERE
(f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/acceler
a.com/contact/%') AND
sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id AND sr.r
_object_id =
s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label = 'Act
ive' AND
(s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT
EXISTS (SELECT
r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_ch
ronicle_id AND
w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w.r_fold
er_path =
f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_format =
'html' AND
w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full
_format =
'zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and
( language_code='en_US'
or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.pare
nt_id from dm_relation r
where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATION_OF'
and r.child_id in
(select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 wh
ere sys1.language_code
in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_
label = 'Active' ) )))
ORDER BY s.i_chronicle_id,s.r_object_id[QUOTE]
CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key
length is 900
bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For s
ome combination of large
values, the insert/update operation will fail.[QUOTE]
>
configurable. Work with
the vendor of the app and see what can be done to handle this. One possible
thing is, of course to
remove the index...
I still suggest you read about what the ROBUST PLAN does. It is likely that
the intermediate steps
of the query processing creates some work table for which the row length exc
eeds the max for SQL
Server.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Raj" <anonymous@.discussions.microsoft.com> wrote in message
news:C6E27085-F620-4B28-8F43-0AD14BD9714B@.microsoft.com...
quote:
> Hi,
> The Query is generated by Documentum. As you know Documentum is a middleware that gener
ates a
DQL Query (SQL like Query) on the underlying database. As per the error desc
ription, it looks like,
I have some Server configuration problem like
quote:
> 1) Maximum length of the index RowSetWorkTableSS = 1020 bytes
> 2) Maximum key length = 900 bytes
> what do these signify? How to increase these from EM?
> -- Tibor Karaszi wrote: --
> You could try adding the ROBUST PLAN hint to your query. Read more ab
out it in Books Online.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=...>
ic.sqlserver
>
> "Raj" <anonymous@.discussions.microsoft.com> wrote in message
> news:65B68D28-7D07-422A-9938-5E4C5E504FA3@.microsoft.com...
> c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label, f.r_fold
er_path
FROM
quote:
> dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f,
dm_sysobject_r sr2 WHERE
> (f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/accelera.com/con
tact/%')
AND
quote:
> sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id A
ND sr.r_object_id =
> s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label
= 'Active' AND
> (s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss')
OR NOT EXISTS (SELECT
> r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id =
s.i_chronicle_id AND
> w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w
.r_folder_path =
> f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_f
ormat = 'html' AND
> w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w
.i_full_format =
> 'zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and ([/color
]
language_code='en_US'
quote:
[color=darkr
ed]
> or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.parent_id fro
m
dm_relation r
quote:
> where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATI
ON_OF' and r.child_id in
> (select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 where[/colo
r]
sys1.language_code
quote:
> in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_label =[/
color]
'Active' ) )))
quote:
> ORDER BY s.i_chronicle_id,s.r_object_id
STATE=01000,[QUOTE]
> CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key length[/c
olor]
is 900
quote:
> bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For some combi
nation
of large[QUOTE]
> values, the insert/update operation will fail.
Friday, March 23, 2012
Query plan
I have 2 SQL Servers with same hardware, configuration and database layout,
but the query plans are different on each server. I tried to look on BOL and
websites and have not find an aswer to why they are different even though
they have same data set. If you have experience this situation before or
know the answer, please help. Thanks!If the data is really the same, as well as the rest of the things you
mention, then I would look at updating statistics on both systems and
see if that brings them closer to using the same plans.
Roy Harvey
Beacon Falls, CT
On Wed, 5 Jul 2006 17:08:02 -0700, KTN <KTN@.discussions.microsoft.com>
wrote:
>I have 2 SQL Servers with same hardware, configuration and database layout,
>but the query plans are different on each server. I tried to look on BOL and
>websites and have not find an aswer to why they are different even though
>they have same data set. If you have experience this situation before or
>know the answer, please help. Thanks!
but the query plans are different on each server. I tried to look on BOL and
websites and have not find an aswer to why they are different even though
they have same data set. If you have experience this situation before or
know the answer, please help. Thanks!If the data is really the same, as well as the rest of the things you
mention, then I would look at updating statistics on both systems and
see if that brings them closer to using the same plans.
Roy Harvey
Beacon Falls, CT
On Wed, 5 Jul 2006 17:08:02 -0700, KTN <KTN@.discussions.microsoft.com>
wrote:
>I have 2 SQL Servers with same hardware, configuration and database layout,
>but the query plans are different on each server. I tried to look on BOL and
>websites and have not find an aswer to why they are different even though
>they have same data set. If you have experience this situation before or
>know the answer, please help. Thanks!
Query plan
I have 2 SQL Servers with same hardware, configuration and database layout,
but the query plans are different on each server. I tried to look on BOL an
d
websites and have not find an aswer to why they are different even though
they have same data set. If you have experience this situation before or
know the answer, please help. Thanks!If the data is really the same, as well as the rest of the things you
mention, then I would look at updating statistics on both systems and
see if that brings them closer to using the same plans.
Roy Harvey
Beacon Falls, CT
On Wed, 5 Jul 2006 17:08:02 -0700, KTN <KTN@.discussions.microsoft.com>
wrote:
>I have 2 SQL Servers with same hardware, configuration and database layout,
>but the query plans are different on each server. I tried to look on BOL a
nd
>websites and have not find an aswer to why they are different even though
>they have same data set. If you have experience this situation before or
>know the answer, please help. Thanks!
but the query plans are different on each server. I tried to look on BOL an
d
websites and have not find an aswer to why they are different even though
they have same data set. If you have experience this situation before or
know the answer, please help. Thanks!If the data is really the same, as well as the rest of the things you
mention, then I would look at updating statistics on both systems and
see if that brings them closer to using the same plans.
Roy Harvey
Beacon Falls, CT
On Wed, 5 Jul 2006 17:08:02 -0700, KTN <KTN@.discussions.microsoft.com>
wrote:
>I have 2 SQL Servers with same hardware, configuration and database layout,
>but the query plans are different on each server. I tried to look on BOL a
nd
>websites and have not find an aswer to why they are different even though
>they have same data set. If you have experience this situation before or
>know the answer, please help. Thanks!
Monday, March 12, 2012
Query Parallelism Configuration
I am working with a SQL Server 7 (SP4) box with 8 processors. When I
bring up Enterprise Manager and go to the Processor tab under server
properties, I can see the configuration for the parallel execution of
queries. In the list box, it is set to use only 1 processor. I set
it to use 2 processors, and click Apply, and then OK. When I go back
to the dialog box, it is set to use only 1 processor again, as if I
had never set the option. When I run sp_configure, it would indicate
that I have, in fact, set the parallel execution to use 2 processors
(max degree of parallelism = 2). It's only from the processor tab
that I think I've only set it to use 1 processor.
Is there a way I can verify that 2 processors are getting used in the
query parallelism? Or is it possible that another configuration
setting is preventing this from happening?
Here is the complete sp_configure, if it helps:
affinity mask 0 2147483647 0 0
allow updates 0 1 1 1
cost threshold for parallelism 0 32767 5 5
cursor threshold -1 2147483647 -1 -1
default language 0 9999 0 0
default sortorder id 0 255 52 52
extended memory size (MB) 0 2147483647 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 1600000 0 0
language in cache 3 100 3 3
language neutral full-text 0 1 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max async IO 1 255 32 32
max degree of parallelism 0 32 2 2
max server memory (MB) 4 2147483647 5500 5500
max text repl size (B) 0 2147483647 65536 65536
max worker threads 10 1024 500 500
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 5500 5500
nested triggers 0 1 1 1
network packet size (B) 512 65535 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 1 1
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 5 5
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
resource timeout (s) 5 2147483647 10 10
scan for startup procs 0 1 0 0
set working set size 0 1 1 1
show advanced options 0 1 1 1
spin counter 1 2147483647 20000 20000
time slice (ms) 50 1000 100 100
two digit year cutoff 1753 9999 2049 2049
Unicode comparison style 0 2147483647 196609 196609
Unicode locale id 0 2147483647 1033 1033
user connections 0 32767 0 0
user options 0 4095 0 0Sp_configure looks right. Might be a bug in EM?
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"AAAWalrus" <aaawalrus@.yahoo.com> wrote in message
news:8b266bc2.0311110819.3541dc88@.posting.google.com...
> I am working with a SQL Server 7 (SP4) box with 8 processors. When I
> bring up Enterprise Manager and go to the Processor tab under server
> properties, I can see the configuration for the parallel execution of
> queries. In the list box, it is set to use only 1 processor. I set
> it to use 2 processors, and click Apply, and then OK. When I go back
> to the dialog box, it is set to use only 1 processor again, as if I
> had never set the option. When I run sp_configure, it would indicate
> that I have, in fact, set the parallel execution to use 2 processors
> (max degree of parallelism = 2). It's only from the processor tab
> that I think I've only set it to use 1 processor.
> Is there a way I can verify that 2 processors are getting used in the
> query parallelism? Or is it possible that another configuration
> setting is preventing this from happening?
> Here is the complete sp_configure, if it helps:
> affinity mask 0 2147483647 0 0
> allow updates 0 1 1 1
> cost threshold for parallelism 0 32767 5 5
> cursor threshold -1 2147483647 -1 -1
> default language 0 9999 0 0
> default sortorder id 0 255 52 52
> extended memory size (MB) 0 2147483647 0 0
> fill factor (%) 0 100 0 0
> index create memory (KB) 704 1600000 0 0
> language in cache 3 100 3 3
> language neutral full-text 0 1 0 0
> lightweight pooling 0 1 0 0
> locks 5000 2147483647 0 0
> max async IO 1 255 32 32
> max degree of parallelism 0 32 2 2
> max server memory (MB) 4 2147483647 5500 5500
> max text repl size (B) 0 2147483647 65536 65536
> max worker threads 10 1024 500 500
> media retention 0 365 0 0
> min memory per query (KB) 512 2147483647 1024 1024
> min server memory (MB) 0 2147483647 5500 5500
> nested triggers 0 1 1 1
> network packet size (B) 512 65535 4096 4096
> open objects 0 2147483647 0 0
> priority boost 0 1 1 1
> query governor cost limit 0 2147483647 0 0
> query wait (s) -1 2147483647 -1 -1
> recovery interval (min) 0 32767 0 0
> remote access 0 1 1 1
> remote login timeout (s) 0 2147483647 5 5
> remote proc trans 0 1 0 0
> remote query timeout (s) 0 2147483647 0 0
> resource timeout (s) 5 2147483647 10 10
> scan for startup procs 0 1 0 0
> set working set size 0 1 1 1
> show advanced options 0 1 1 1
> spin counter 1 2147483647 20000 20000
> time slice (ms) 50 1000 100 100
> two digit year cutoff 1753 9999 2049 2049
> Unicode comparison style 0 2147483647 196609 196609
> Unicode locale id 0 2147483647 1033 1033
> user connections 0 32767 0 0
> user options 0 4095 0 0|||You can either inspect the query execution plan (should see parallelism for
queries/subqueries that cost more than 5)
or/and to take a look in Processor Usage in Performance Monitor if you can
afford to be the only one to make traffic on the server
at certain time
Uzytkownik "AAAWalrus" <aaawalrus@.yahoo.com> napisal w wiadomosci
news:8b266bc2.0311110819.3541dc88@.posting.google.com...
> I am working with a SQL Server 7 (SP4) box with 8 processors. When I
> bring up Enterprise Manager and go to the Processor tab under server
> properties, I can see the configuration for the parallel execution of
> queries. In the list box, it is set to use only 1 processor. I set
> it to use 2 processors, and click Apply, and then OK. When I go back
> to the dialog box, it is set to use only 1 processor again, as if I
> had never set the option. When I run sp_configure, it would indicate
> that I have, in fact, set the parallel execution to use 2 processors
> (max degree of parallelism = 2). It's only from the processor tab
> that I think I've only set it to use 1 processor.
> Is there a way I can verify that 2 processors are getting used in the
> query parallelism? Or is it possible that another configuration
> setting is preventing this from happening?
> Here is the complete sp_configure, if it helps:
> affinity mask 0 2147483647 0 0
> allow updates 0 1 1 1
> cost threshold for parallelism 0 32767 5 5
> cursor threshold -1 2147483647 -1 -1
> default language 0 9999 0 0
> default sortorder id 0 255 52 52
> extended memory size (MB) 0 2147483647 0 0
> fill factor (%) 0 100 0 0
> index create memory (KB) 704 1600000 0 0
> language in cache 3 100 3 3
> language neutral full-text 0 1 0 0
> lightweight pooling 0 1 0 0
> locks 5000 2147483647 0 0
> max async IO 1 255 32 32
> max degree of parallelism 0 32 2 2
> max server memory (MB) 4 2147483647 5500 5500
> max text repl size (B) 0 2147483647 65536 65536
> max worker threads 10 1024 500 500
> media retention 0 365 0 0
> min memory per query (KB) 512 2147483647 1024 1024
> min server memory (MB) 0 2147483647 5500 5500
> nested triggers 0 1 1 1
> network packet size (B) 512 65535 4096 4096
> open objects 0 2147483647 0 0
> priority boost 0 1 1 1
> query governor cost limit 0 2147483647 0 0
> query wait (s) -1 2147483647 -1 -1
> recovery interval (min) 0 32767 0 0
> remote access 0 1 1 1
> remote login timeout (s) 0 2147483647 5 5
> remote proc trans 0 1 0 0
> remote query timeout (s) 0 2147483647 0 0
> resource timeout (s) 5 2147483647 10 10
> scan for startup procs 0 1 0 0
> set working set size 0 1 1 1
> show advanced options 0 1 1 1
> spin counter 1 2147483647 20000 20000
> time slice (ms) 50 1000 100 100
> two digit year cutoff 1753 9999 2049 2049
> Unicode comparison style 0 2147483647 196609 196609
> Unicode locale id 0 2147483647 1033 1033
> user connections 0 32767 0 0
> user options 0 4095 0 0|||the best way is to use profiler, capture the classes for degree of
parallelism.
BTW, just having a cost of 5 does not guarentee parallism, only certain
query plan operators can be run in parallel.
--
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Tomasz" <tp@.nospam.com> wrote in message
news:efKp2EHqDHA.2444@.TK2MSFTNGP09.phx.gbl...
> You can either inspect the query execution plan (should see parallelism
for
> queries/subqueries that cost more than 5)
> or/and to take a look in Processor Usage in Performance Monitor if you can
> afford to be the only one to make traffic on the server
> at certain time
> Uzytkownik "AAAWalrus" <aaawalrus@.yahoo.com> napisal w wiadomosci
> news:8b266bc2.0311110819.3541dc88@.posting.google.com...
> > I am working with a SQL Server 7 (SP4) box with 8 processors. When I
> > bring up Enterprise Manager and go to the Processor tab under server
> > properties, I can see the configuration for the parallel execution of
> > queries. In the list box, it is set to use only 1 processor. I set
> > it to use 2 processors, and click Apply, and then OK. When I go back
> > to the dialog box, it is set to use only 1 processor again, as if I
> > had never set the option. When I run sp_configure, it would indicate
> > that I have, in fact, set the parallel execution to use 2 processors
> > (max degree of parallelism = 2). It's only from the processor tab
> > that I think I've only set it to use 1 processor.
> >
> > Is there a way I can verify that 2 processors are getting used in the
> > query parallelism? Or is it possible that another configuration
> > setting is preventing this from happening?
> >
> > Here is the complete sp_configure, if it helps:
> >
> > affinity mask 0 2147483647 0 0
> > allow updates 0 1 1 1
> > cost threshold for parallelism 0 32767 5 5
> > cursor threshold -1 2147483647 -1 -1
> > default language 0 9999 0 0
> > default sortorder id 0 255 52 52
> > extended memory size (MB) 0 2147483647 0 0
> > fill factor (%) 0 100 0 0
> > index create memory (KB) 704 1600000 0 0
> > language in cache 3 100 3 3
> > language neutral full-text 0 1 0 0
> > lightweight pooling 0 1 0 0
> > locks 5000 2147483647 0 0
> > max async IO 1 255 32 32
> > max degree of parallelism 0 32 2 2
> > max server memory (MB) 4 2147483647 5500 5500
> > max text repl size (B) 0 2147483647 65536 65536
> > max worker threads 10 1024 500 500
> > media retention 0 365 0 0
> > min memory per query (KB) 512 2147483647 1024 1024
> > min server memory (MB) 0 2147483647 5500 5500
> > nested triggers 0 1 1 1
> > network packet size (B) 512 65535 4096 4096
> > open objects 0 2147483647 0 0
> > priority boost 0 1 1 1
> > query governor cost limit 0 2147483647 0 0
> > query wait (s) -1 2147483647 -1 -1
> > recovery interval (min) 0 32767 0 0
> > remote access 0 1 1 1
> > remote login timeout (s) 0 2147483647 5 5
> > remote proc trans 0 1 0 0
> > remote query timeout (s) 0 2147483647 0 0
> > resource timeout (s) 5 2147483647 10 10
> > scan for startup procs 0 1 0 0
> > set working set size 0 1 1 1
> > show advanced options 0 1 1 1
> > spin counter 1 2147483647 20000 20000
> > time slice (ms) 50 1000 100 100
> > two digit year cutoff 1753 9999 2049 2049
> > Unicode comparison style 0 2147483647 196609 196609
> > Unicode locale id 0 2147483647 1033 1033
> > user connections 0 32767 0 0
> > user options 0 4095 0 0
>|||I am chalking this up to a bug in the SQL 7 Enterprise Manager. When
I view the same option on the SQL 7 database from SQL 2000 Enterprise
Manager (personal edition installed on my PC), it properly updates and
shows the number of processors to use on parallel queries. What makes
me a little worried, though, is that I have not found a relative MS
support article. I've been contemplating calling MS support about it,
but that's a whole other set of hassles.
Thanks for your help!
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message news:<#JdolkHqDHA.1676@.TK2MSFTNGP09.phx.gbl>...
> the best way is to use profiler, capture the classes for degree of
> parallelism.
> BTW, just having a cost of 5 does not guarentee parallism, only certain
> query plan operators can be run in parallel.
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "Tomasz" <tp@.nospam.com> wrote in message
> news:efKp2EHqDHA.2444@.TK2MSFTNGP09.phx.gbl...
> > You can either inspect the query execution plan (should see parallelism
> for
> > queries/subqueries that cost more than 5)
> > or/and to take a look in Processor Usage in Performance Monitor if you can
> > afford to be the only one to make traffic on the server
> > at certain time
bring up Enterprise Manager and go to the Processor tab under server
properties, I can see the configuration for the parallel execution of
queries. In the list box, it is set to use only 1 processor. I set
it to use 2 processors, and click Apply, and then OK. When I go back
to the dialog box, it is set to use only 1 processor again, as if I
had never set the option. When I run sp_configure, it would indicate
that I have, in fact, set the parallel execution to use 2 processors
(max degree of parallelism = 2). It's only from the processor tab
that I think I've only set it to use 1 processor.
Is there a way I can verify that 2 processors are getting used in the
query parallelism? Or is it possible that another configuration
setting is preventing this from happening?
Here is the complete sp_configure, if it helps:
affinity mask 0 2147483647 0 0
allow updates 0 1 1 1
cost threshold for parallelism 0 32767 5 5
cursor threshold -1 2147483647 -1 -1
default language 0 9999 0 0
default sortorder id 0 255 52 52
extended memory size (MB) 0 2147483647 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 1600000 0 0
language in cache 3 100 3 3
language neutral full-text 0 1 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max async IO 1 255 32 32
max degree of parallelism 0 32 2 2
max server memory (MB) 4 2147483647 5500 5500
max text repl size (B) 0 2147483647 65536 65536
max worker threads 10 1024 500 500
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 5500 5500
nested triggers 0 1 1 1
network packet size (B) 512 65535 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 1 1
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 5 5
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
resource timeout (s) 5 2147483647 10 10
scan for startup procs 0 1 0 0
set working set size 0 1 1 1
show advanced options 0 1 1 1
spin counter 1 2147483647 20000 20000
time slice (ms) 50 1000 100 100
two digit year cutoff 1753 9999 2049 2049
Unicode comparison style 0 2147483647 196609 196609
Unicode locale id 0 2147483647 1033 1033
user connections 0 32767 0 0
user options 0 4095 0 0Sp_configure looks right. Might be a bug in EM?
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"AAAWalrus" <aaawalrus@.yahoo.com> wrote in message
news:8b266bc2.0311110819.3541dc88@.posting.google.com...
> I am working with a SQL Server 7 (SP4) box with 8 processors. When I
> bring up Enterprise Manager and go to the Processor tab under server
> properties, I can see the configuration for the parallel execution of
> queries. In the list box, it is set to use only 1 processor. I set
> it to use 2 processors, and click Apply, and then OK. When I go back
> to the dialog box, it is set to use only 1 processor again, as if I
> had never set the option. When I run sp_configure, it would indicate
> that I have, in fact, set the parallel execution to use 2 processors
> (max degree of parallelism = 2). It's only from the processor tab
> that I think I've only set it to use 1 processor.
> Is there a way I can verify that 2 processors are getting used in the
> query parallelism? Or is it possible that another configuration
> setting is preventing this from happening?
> Here is the complete sp_configure, if it helps:
> affinity mask 0 2147483647 0 0
> allow updates 0 1 1 1
> cost threshold for parallelism 0 32767 5 5
> cursor threshold -1 2147483647 -1 -1
> default language 0 9999 0 0
> default sortorder id 0 255 52 52
> extended memory size (MB) 0 2147483647 0 0
> fill factor (%) 0 100 0 0
> index create memory (KB) 704 1600000 0 0
> language in cache 3 100 3 3
> language neutral full-text 0 1 0 0
> lightweight pooling 0 1 0 0
> locks 5000 2147483647 0 0
> max async IO 1 255 32 32
> max degree of parallelism 0 32 2 2
> max server memory (MB) 4 2147483647 5500 5500
> max text repl size (B) 0 2147483647 65536 65536
> max worker threads 10 1024 500 500
> media retention 0 365 0 0
> min memory per query (KB) 512 2147483647 1024 1024
> min server memory (MB) 0 2147483647 5500 5500
> nested triggers 0 1 1 1
> network packet size (B) 512 65535 4096 4096
> open objects 0 2147483647 0 0
> priority boost 0 1 1 1
> query governor cost limit 0 2147483647 0 0
> query wait (s) -1 2147483647 -1 -1
> recovery interval (min) 0 32767 0 0
> remote access 0 1 1 1
> remote login timeout (s) 0 2147483647 5 5
> remote proc trans 0 1 0 0
> remote query timeout (s) 0 2147483647 0 0
> resource timeout (s) 5 2147483647 10 10
> scan for startup procs 0 1 0 0
> set working set size 0 1 1 1
> show advanced options 0 1 1 1
> spin counter 1 2147483647 20000 20000
> time slice (ms) 50 1000 100 100
> two digit year cutoff 1753 9999 2049 2049
> Unicode comparison style 0 2147483647 196609 196609
> Unicode locale id 0 2147483647 1033 1033
> user connections 0 32767 0 0
> user options 0 4095 0 0|||You can either inspect the query execution plan (should see parallelism for
queries/subqueries that cost more than 5)
or/and to take a look in Processor Usage in Performance Monitor if you can
afford to be the only one to make traffic on the server
at certain time
Uzytkownik "AAAWalrus" <aaawalrus@.yahoo.com> napisal w wiadomosci
news:8b266bc2.0311110819.3541dc88@.posting.google.com...
> I am working with a SQL Server 7 (SP4) box with 8 processors. When I
> bring up Enterprise Manager and go to the Processor tab under server
> properties, I can see the configuration for the parallel execution of
> queries. In the list box, it is set to use only 1 processor. I set
> it to use 2 processors, and click Apply, and then OK. When I go back
> to the dialog box, it is set to use only 1 processor again, as if I
> had never set the option. When I run sp_configure, it would indicate
> that I have, in fact, set the parallel execution to use 2 processors
> (max degree of parallelism = 2). It's only from the processor tab
> that I think I've only set it to use 1 processor.
> Is there a way I can verify that 2 processors are getting used in the
> query parallelism? Or is it possible that another configuration
> setting is preventing this from happening?
> Here is the complete sp_configure, if it helps:
> affinity mask 0 2147483647 0 0
> allow updates 0 1 1 1
> cost threshold for parallelism 0 32767 5 5
> cursor threshold -1 2147483647 -1 -1
> default language 0 9999 0 0
> default sortorder id 0 255 52 52
> extended memory size (MB) 0 2147483647 0 0
> fill factor (%) 0 100 0 0
> index create memory (KB) 704 1600000 0 0
> language in cache 3 100 3 3
> language neutral full-text 0 1 0 0
> lightweight pooling 0 1 0 0
> locks 5000 2147483647 0 0
> max async IO 1 255 32 32
> max degree of parallelism 0 32 2 2
> max server memory (MB) 4 2147483647 5500 5500
> max text repl size (B) 0 2147483647 65536 65536
> max worker threads 10 1024 500 500
> media retention 0 365 0 0
> min memory per query (KB) 512 2147483647 1024 1024
> min server memory (MB) 0 2147483647 5500 5500
> nested triggers 0 1 1 1
> network packet size (B) 512 65535 4096 4096
> open objects 0 2147483647 0 0
> priority boost 0 1 1 1
> query governor cost limit 0 2147483647 0 0
> query wait (s) -1 2147483647 -1 -1
> recovery interval (min) 0 32767 0 0
> remote access 0 1 1 1
> remote login timeout (s) 0 2147483647 5 5
> remote proc trans 0 1 0 0
> remote query timeout (s) 0 2147483647 0 0
> resource timeout (s) 5 2147483647 10 10
> scan for startup procs 0 1 0 0
> set working set size 0 1 1 1
> show advanced options 0 1 1 1
> spin counter 1 2147483647 20000 20000
> time slice (ms) 50 1000 100 100
> two digit year cutoff 1753 9999 2049 2049
> Unicode comparison style 0 2147483647 196609 196609
> Unicode locale id 0 2147483647 1033 1033
> user connections 0 32767 0 0
> user options 0 4095 0 0|||the best way is to use profiler, capture the classes for degree of
parallelism.
BTW, just having a cost of 5 does not guarentee parallism, only certain
query plan operators can be run in parallel.
--
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Tomasz" <tp@.nospam.com> wrote in message
news:efKp2EHqDHA.2444@.TK2MSFTNGP09.phx.gbl...
> You can either inspect the query execution plan (should see parallelism
for
> queries/subqueries that cost more than 5)
> or/and to take a look in Processor Usage in Performance Monitor if you can
> afford to be the only one to make traffic on the server
> at certain time
> Uzytkownik "AAAWalrus" <aaawalrus@.yahoo.com> napisal w wiadomosci
> news:8b266bc2.0311110819.3541dc88@.posting.google.com...
> > I am working with a SQL Server 7 (SP4) box with 8 processors. When I
> > bring up Enterprise Manager and go to the Processor tab under server
> > properties, I can see the configuration for the parallel execution of
> > queries. In the list box, it is set to use only 1 processor. I set
> > it to use 2 processors, and click Apply, and then OK. When I go back
> > to the dialog box, it is set to use only 1 processor again, as if I
> > had never set the option. When I run sp_configure, it would indicate
> > that I have, in fact, set the parallel execution to use 2 processors
> > (max degree of parallelism = 2). It's only from the processor tab
> > that I think I've only set it to use 1 processor.
> >
> > Is there a way I can verify that 2 processors are getting used in the
> > query parallelism? Or is it possible that another configuration
> > setting is preventing this from happening?
> >
> > Here is the complete sp_configure, if it helps:
> >
> > affinity mask 0 2147483647 0 0
> > allow updates 0 1 1 1
> > cost threshold for parallelism 0 32767 5 5
> > cursor threshold -1 2147483647 -1 -1
> > default language 0 9999 0 0
> > default sortorder id 0 255 52 52
> > extended memory size (MB) 0 2147483647 0 0
> > fill factor (%) 0 100 0 0
> > index create memory (KB) 704 1600000 0 0
> > language in cache 3 100 3 3
> > language neutral full-text 0 1 0 0
> > lightweight pooling 0 1 0 0
> > locks 5000 2147483647 0 0
> > max async IO 1 255 32 32
> > max degree of parallelism 0 32 2 2
> > max server memory (MB) 4 2147483647 5500 5500
> > max text repl size (B) 0 2147483647 65536 65536
> > max worker threads 10 1024 500 500
> > media retention 0 365 0 0
> > min memory per query (KB) 512 2147483647 1024 1024
> > min server memory (MB) 0 2147483647 5500 5500
> > nested triggers 0 1 1 1
> > network packet size (B) 512 65535 4096 4096
> > open objects 0 2147483647 0 0
> > priority boost 0 1 1 1
> > query governor cost limit 0 2147483647 0 0
> > query wait (s) -1 2147483647 -1 -1
> > recovery interval (min) 0 32767 0 0
> > remote access 0 1 1 1
> > remote login timeout (s) 0 2147483647 5 5
> > remote proc trans 0 1 0 0
> > remote query timeout (s) 0 2147483647 0 0
> > resource timeout (s) 5 2147483647 10 10
> > scan for startup procs 0 1 0 0
> > set working set size 0 1 1 1
> > show advanced options 0 1 1 1
> > spin counter 1 2147483647 20000 20000
> > time slice (ms) 50 1000 100 100
> > two digit year cutoff 1753 9999 2049 2049
> > Unicode comparison style 0 2147483647 196609 196609
> > Unicode locale id 0 2147483647 1033 1033
> > user connections 0 32767 0 0
> > user options 0 4095 0 0
>|||I am chalking this up to a bug in the SQL 7 Enterprise Manager. When
I view the same option on the SQL 7 database from SQL 2000 Enterprise
Manager (personal edition installed on my PC), it properly updates and
shows the number of processors to use on parallel queries. What makes
me a little worried, though, is that I have not found a relative MS
support article. I've been contemplating calling MS support about it,
but that's a whole other set of hassles.
Thanks for your help!
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message news:<#JdolkHqDHA.1676@.TK2MSFTNGP09.phx.gbl>...
> the best way is to use profiler, capture the classes for degree of
> parallelism.
> BTW, just having a cost of 5 does not guarentee parallism, only certain
> query plan operators can be run in parallel.
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "Tomasz" <tp@.nospam.com> wrote in message
> news:efKp2EHqDHA.2444@.TK2MSFTNGP09.phx.gbl...
> > You can either inspect the query execution plan (should see parallelism
> for
> > queries/subqueries that cost more than 5)
> > or/and to take a look in Processor Usage in Performance Monitor if you can
> > afford to be the only one to make traffic on the server
> > at certain time
Subscribe to:
Posts (Atom)