Hi,
I am about to start system optimization and I have a general question about
how to proceed.
I am a coldfusion programmer and working on a lagre system with a lot of db
interaction. Recently, our isp upgraded to SQL 2000 and I started using
functions quite extensively for validation and calculations. I have a lot of
large queries that I am planning to transfer from coldfusion scripts to sql.
My question is:
SQL Server has few options for queries: stored procedures (I use them a lot
for complex logic), views and functions. What should I use for queries? For
example views can be indexed same as tables, stored procedures are compiled
and last one is functions (I dont think is such a good idea to store queries
but they can return tables). What are general recommendations?
Thanks in advance,
GeneThe answer: It depends.
If you can take part of your complex operation and make an indexed view, it
is certainly worthwhile, esp. if this view of information is used in more
than one procedure.
If the index will not help and there is no reuse, the procedures are the
best avenue for most work that performs CRUD on one or more tables.
Functions are used to solve problems outside of the data set realm (at
least, in general), like special calculations of aggregations or date
formatting outside of what is natively in SQL Server. For example, we had a
function like:
SELECT OurCustomerDateFormat(DateField) FROM SomeTable
That was a function.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"News" wrote:
> Hi,
> I am about to start system optimization and I have a general question abou
t
> how to proceed.
> I am a coldfusion programmer and working on a lagre system with a lot of d
b
> interaction. Recently, our isp upgraded to SQL 2000 and I started using
> functions quite extensively for validation and calculations. I have a lot
of
> large queries that I am planning to transfer from coldfusion scripts to sq
l.
> My question is:
> SQL Server has few options for queries: stored procedures (I use them a lo
t
> for complex logic), views and functions. What should I use for queries? Fo
r
> example views can be indexed same as tables, stored procedures are compile
d
> and last one is functions (I dont think is such a good idea to store queri
es
> but they can return tables). What are general recommendations?
> Thanks in advance,
> Gene
>
>|||>> I started using functions quite extensively for validation and calculatio
ns. <<
Usually a bad idea for validation. SQL is declarative and you want to
use CHECK() and DEFAULT clauses that apply to an entire column, rather
than triggers, functions and procedures which work on scalars.
I would go with stored procedures and VIEWs for the calculations.
Functions that return tables and indexed views are proprietary.
Showing posts with label system. Show all posts
Showing posts with label system. Show all posts
Friday, March 30, 2012
Optimizing Lookups on a trigger's INSERTED virtual table
Hi!
I have a problem with a stored procedure I am analizing, it is a very
critical piece of a system I am developing, this stored procedure should be
the only point of access to update rows in a table, this is for concurrency
control and transaction level is set to serializable.
Now the main problem I am having right now is that when I analyze the
execution of this piece of code in query analyzer the trigger that verifies
the information being inserted is valid takes 39% of the total execution time
for the stored procedure only to execute an "INSERTED SCAN" on the trigger's
INSERTED table (which should always contain only one row). I added TOP 1 and
WITH(fastfirstrow) trying to optimize this operation but got no decrease in
the relative weight of this statement.
Also when I call this sp 10000 times in my test environment with query
analyzer I may get at least 30 events with duration above 100 and at least 5
with more than 1000.
Any tips, specially with the INSERTED SCAN, I could not find any place in
the internet with a strategy to optimize this operation...
Thanks!!!
Post your trigger code and a CREATE TABLE statement for the table.
You mentioned that INSERTED "should always contain only one row". Set-based
code is usually much more efficient anyway so the number of rows affected
should be irrelevant. Don't assign values to variables in a trigger because
multiple procedural statements in triggers will create a bottleneck. For the
same reason it seems unwise and unecessary to add TOP 1 to trigger code
statements. It will make hard work for the DBA to fix data quality issues or
schema changes if he/she is forced to update only one row at a time.
David Portas
SQL Server MVP
|||Thanks a lot David!
Can you recommend good set-based sql programming tutorials, I have heard a
lot about it but I couldn't find any good source of information.
Thanks again! :D
Ignacio
"David Portas" wrote:
> Post your trigger code and a CREATE TABLE statement for the table.
> You mentioned that INSERTED "should always contain only one row". Set-based
> code is usually much more efficient anyway so the number of rows affected
> should be irrelevant. Don't assign values to variables in a trigger because
> multiple procedural statements in triggers will create a bottleneck. For the
> same reason it seems unwise and unecessary to add TOP 1 to trigger code
> statements. It will make hard work for the DBA to fix data quality issues or
> schema changes if he/she is forced to update only one row at a time.
> --
> David Portas
> SQL Server MVP
> --
>
>
I have a problem with a stored procedure I am analizing, it is a very
critical piece of a system I am developing, this stored procedure should be
the only point of access to update rows in a table, this is for concurrency
control and transaction level is set to serializable.
Now the main problem I am having right now is that when I analyze the
execution of this piece of code in query analyzer the trigger that verifies
the information being inserted is valid takes 39% of the total execution time
for the stored procedure only to execute an "INSERTED SCAN" on the trigger's
INSERTED table (which should always contain only one row). I added TOP 1 and
WITH(fastfirstrow) trying to optimize this operation but got no decrease in
the relative weight of this statement.
Also when I call this sp 10000 times in my test environment with query
analyzer I may get at least 30 events with duration above 100 and at least 5
with more than 1000.
Any tips, specially with the INSERTED SCAN, I could not find any place in
the internet with a strategy to optimize this operation...
Thanks!!!
Post your trigger code and a CREATE TABLE statement for the table.
You mentioned that INSERTED "should always contain only one row". Set-based
code is usually much more efficient anyway so the number of rows affected
should be irrelevant. Don't assign values to variables in a trigger because
multiple procedural statements in triggers will create a bottleneck. For the
same reason it seems unwise and unecessary to add TOP 1 to trigger code
statements. It will make hard work for the DBA to fix data quality issues or
schema changes if he/she is forced to update only one row at a time.
David Portas
SQL Server MVP
|||Thanks a lot David!
Can you recommend good set-based sql programming tutorials, I have heard a
lot about it but I couldn't find any good source of information.
Thanks again! :D
Ignacio
"David Portas" wrote:
> Post your trigger code and a CREATE TABLE statement for the table.
> You mentioned that INSERTED "should always contain only one row". Set-based
> code is usually much more efficient anyway so the number of rows affected
> should be irrelevant. Don't assign values to variables in a trigger because
> multiple procedural statements in triggers will create a bottleneck. For the
> same reason it seems unwise and unecessary to add TOP 1 to trigger code
> statements. It will make hard work for the DBA to fix data quality issues or
> schema changes if he/she is forced to update only one row at a time.
> --
> David Portas
> SQL Server MVP
> --
>
>
Optimizing Lookups on a trigger's INSERTED virtual table
Hi!
I have a problem with a stored procedure I am analizing, it is a very
critical piece of a system I am developing, this stored procedure should be
the only point of access to update rows in a table, this is for concurrency
control and transaction level is set to serializable.
Now the main problem I am having right now is that when I analyze the
execution of this piece of code in query analyzer the trigger that verifies
the information being inserted is valid takes 39% of the total execution time
for the stored procedure only to execute an "INSERTED SCAN" on the trigger's
INSERTED table (which should always contain only one row). I added TOP 1 and
WITH(fastfirstrow) trying to optimize this operation but got no decrease in
the relative weight of this statement.
Also when I call this sp 10000 times in my test environment with query
analyzer I may get at least 30 events with duration above 100 and at least 5
with more than 1000.
Any tips, specially with the INSERTED SCAN, I could not find any place in
the internet with a strategy to optimize this operation...
Thanks!!!Post your trigger code and a CREATE TABLE statement for the table.
You mentioned that INSERTED "should always contain only one row". Set-based
code is usually much more efficient anyway so the number of rows affected
should be irrelevant. Don't assign values to variables in a trigger because
multiple procedural statements in triggers will create a bottleneck. For the
same reason it seems unwise and unecessary to add TOP 1 to trigger code
statements. It will make hard work for the DBA to fix data quality issues or
schema changes if he/she is forced to update only one row at a time.
--
David Portas
SQL Server MVP
--|||Thanks a lot David!
Can you recommend good set-based sql programming tutorials, I have heard a
lot about it but I couldn't find any good source of information.
Thanks again! :D
Ignacio
"David Portas" wrote:
> Post your trigger code and a CREATE TABLE statement for the table.
> You mentioned that INSERTED "should always contain only one row". Set-based
> code is usually much more efficient anyway so the number of rows affected
> should be irrelevant. Don't assign values to variables in a trigger because
> multiple procedural statements in triggers will create a bottleneck. For the
> same reason it seems unwise and unecessary to add TOP 1 to trigger code
> statements. It will make hard work for the DBA to fix data quality issues or
> schema changes if he/she is forced to update only one row at a time.
> --
> David Portas
> SQL Server MVP
> --
>
>sql
I have a problem with a stored procedure I am analizing, it is a very
critical piece of a system I am developing, this stored procedure should be
the only point of access to update rows in a table, this is for concurrency
control and transaction level is set to serializable.
Now the main problem I am having right now is that when I analyze the
execution of this piece of code in query analyzer the trigger that verifies
the information being inserted is valid takes 39% of the total execution time
for the stored procedure only to execute an "INSERTED SCAN" on the trigger's
INSERTED table (which should always contain only one row). I added TOP 1 and
WITH(fastfirstrow) trying to optimize this operation but got no decrease in
the relative weight of this statement.
Also when I call this sp 10000 times in my test environment with query
analyzer I may get at least 30 events with duration above 100 and at least 5
with more than 1000.
Any tips, specially with the INSERTED SCAN, I could not find any place in
the internet with a strategy to optimize this operation...
Thanks!!!Post your trigger code and a CREATE TABLE statement for the table.
You mentioned that INSERTED "should always contain only one row". Set-based
code is usually much more efficient anyway so the number of rows affected
should be irrelevant. Don't assign values to variables in a trigger because
multiple procedural statements in triggers will create a bottleneck. For the
same reason it seems unwise and unecessary to add TOP 1 to trigger code
statements. It will make hard work for the DBA to fix data quality issues or
schema changes if he/she is forced to update only one row at a time.
--
David Portas
SQL Server MVP
--|||Thanks a lot David!
Can you recommend good set-based sql programming tutorials, I have heard a
lot about it but I couldn't find any good source of information.
Thanks again! :D
Ignacio
"David Portas" wrote:
> Post your trigger code and a CREATE TABLE statement for the table.
> You mentioned that INSERTED "should always contain only one row". Set-based
> code is usually much more efficient anyway so the number of rows affected
> should be irrelevant. Don't assign values to variables in a trigger because
> multiple procedural statements in triggers will create a bottleneck. For the
> same reason it seems unwise and unecessary to add TOP 1 to trigger code
> statements. It will make hard work for the DBA to fix data quality issues or
> schema changes if he/she is forced to update only one row at a time.
> --
> David Portas
> SQL Server MVP
> --
>
>sql
Optimizing Lookups on a trigger's INSERTED virtual table
Hi!
I have a problem with a stored procedure I am analizing, it is a very
critical piece of a system I am developing, this stored procedure should be
the only point of access to update rows in a table, this is for concurrency
control and transaction level is set to serializable.
Now the main problem I am having right now is that when I analyze the
execution of this piece of code in query analyzer the trigger that verifies
the information being inserted is valid takes 39% of the total execution tim
e
for the stored procedure only to execute an "INSERTED SCAN" on the trigger's
INSERTED table (which should always contain only one row). I added TOP 1 and
WITH(fastfirstrow) trying to optimize this operation but got no decrease in
the relative weight of this statement.
Also when I call this sp 10000 times in my test environment with query
analyzer I may get at least 30 events with duration above 100 and at least 5
with more than 1000.
Any tips, specially with the INSERTED SCAN, I could not find any place in
the internet with a strategy to optimize this operation...
Thanks!!!Post your trigger code and a CREATE TABLE statement for the table.
You mentioned that INSERTED "should always contain only one row". Set-based
code is usually much more efficient anyway so the number of rows affected
should be irrelevant. Don't assign values to variables in a trigger because
multiple procedural statements in triggers will create a bottleneck. For the
same reason it seems unwise and unecessary to add TOP 1 to trigger code
statements. It will make hard work for the DBA to fix data quality issues or
schema changes if he/she is forced to update only one row at a time.
David Portas
SQL Server MVP
--|||Thanks a lot David!
Can you recommend good set-based sql programming tutorials, I have heard a
lot about it but I couldn't find any good source of information.
Thanks again! :D
Ignacio
"David Portas" wrote:
> Post your trigger code and a CREATE TABLE statement for the table.
> You mentioned that INSERTED "should always contain only one row". Set-base
d
> code is usually much more efficient anyway so the number of rows affected
> should be irrelevant. Don't assign values to variables in a trigger becaus
e
> multiple procedural statements in triggers will create a bottleneck. For t
he
> same reason it seems unwise and unecessary to add TOP 1 to trigger code
> statements. It will make hard work for the DBA to fix data quality issues
or
> schema changes if he/she is forced to update only one row at a time.
> --
> David Portas
> SQL Server MVP
> --
>
>
I have a problem with a stored procedure I am analizing, it is a very
critical piece of a system I am developing, this stored procedure should be
the only point of access to update rows in a table, this is for concurrency
control and transaction level is set to serializable.
Now the main problem I am having right now is that when I analyze the
execution of this piece of code in query analyzer the trigger that verifies
the information being inserted is valid takes 39% of the total execution tim
e
for the stored procedure only to execute an "INSERTED SCAN" on the trigger's
INSERTED table (which should always contain only one row). I added TOP 1 and
WITH(fastfirstrow) trying to optimize this operation but got no decrease in
the relative weight of this statement.
Also when I call this sp 10000 times in my test environment with query
analyzer I may get at least 30 events with duration above 100 and at least 5
with more than 1000.
Any tips, specially with the INSERTED SCAN, I could not find any place in
the internet with a strategy to optimize this operation...
Thanks!!!Post your trigger code and a CREATE TABLE statement for the table.
You mentioned that INSERTED "should always contain only one row". Set-based
code is usually much more efficient anyway so the number of rows affected
should be irrelevant. Don't assign values to variables in a trigger because
multiple procedural statements in triggers will create a bottleneck. For the
same reason it seems unwise and unecessary to add TOP 1 to trigger code
statements. It will make hard work for the DBA to fix data quality issues or
schema changes if he/she is forced to update only one row at a time.
David Portas
SQL Server MVP
--|||Thanks a lot David!
Can you recommend good set-based sql programming tutorials, I have heard a
lot about it but I couldn't find any good source of information.
Thanks again! :D
Ignacio
"David Portas" wrote:
> Post your trigger code and a CREATE TABLE statement for the table.
> You mentioned that INSERTED "should always contain only one row". Set-base
d
> code is usually much more efficient anyway so the number of rows affected
> should be irrelevant. Don't assign values to variables in a trigger becaus
e
> multiple procedural statements in triggers will create a bottleneck. For t
he
> same reason it seems unwise and unecessary to add TOP 1 to trigger code
> statements. It will make hard work for the DBA to fix data quality issues
or
> schema changes if he/she is forced to update only one row at a time.
> --
> David Portas
> SQL Server MVP
> --
>
>
Friday, March 23, 2012
Optimizations on System Tables
Hi
I have an issue where my system tables are heavily fragmented. Currently our system creates between 200 and 400 new logins daily (its the method the application handles it security). However we have had execution times on this of between 13ms and 1900ms, very inconsistent.
I have seen in a previous thread http://dbforums.com/arch/70/2003/11/965415
that its Kalen Delaney's opinion that fragmentation doesn't really matter unless you have 'several dozen' pages
Here is an example of our sysxlogins table's report :
DBCC SHOWCONTIG scanning 'sysxlogins' table...
Table: 'sysxlogins' (33); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 162
- Extents Scanned.......................: 35
- Extent Switches.......................: 156
- Avg. Pages per Extent..................: 4.6
- Scan Density [Best Count:Actual Count]......: 13.38% [21:157]
- Logical Scan Fragmentation ..............: 49.38%
- Extent Scan Fragmentation ...............: 34.29%
- Avg. Bytes Free per Page................: 5694.6
- Avg. Page Density (full)................: 29.64%
As you can see, its got 162 pages, which probably counts as 'several' dozen.
I would like to know if its possible for us to defrag/reindex this table, as you can see, it has a VERY low Scan Density, and a rather high Fragmentation level.
Opinions, help, undocumented functionality all welcome
TiaanI am facing a same issue.
I have noticed that my system table are extremly fragmented. Take a look. Any suggestions? Or does it not matter that they are like that?
DBCC SHOWCONTIG scanning 'sysobjects' table...
Table: 'sysobjects' (1); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 23
- Extents Scanned.......................: 7
- Extent Switches.......................: 21
- Avg. Pages per Extent..................: 3.3
- Scan Density [Best Count:Actual Count]......: 13.64% [3:22]
- Logical Scan Fragmentation ..............: 43.48%
- Extent Scan Fragmentation ...............: 71.43%
- Avg. Bytes Free per Page................: 2744.6
- Avg. Page Density (full)................: 66.09%
DBCC SHOWCONTIG scanning 'sysindexes' table...
Table: 'sysindexes' (2); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 4
- Extents Scanned.......................: 4
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 25.00%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 3019.0
- Avg. Page Density (full)................: 62.70%
DBCC SHOWCONTIG scanning 'syscolumns' table...
Table: 'syscolumns' (3); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 85
- Extents Scanned.......................: 17
- Extent Switches.......................: 81
- Avg. Pages per Extent..................: 5.0
- Scan Density [Best Count:Actual Count]......: 13.41% [11:82]
- Logical Scan Fragmentation ..............: 44.71%
- Extent Scan Fragmentation ...............: 70.59%
- Avg. Bytes Free per Page................: 3348.5
- Avg. Page Density (full)................: 58.63%
DBCC SHOWCONTIG scanning 'syscomments' table...
Table: 'syscomments' (6); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 984
- Extents Scanned.......................: 159
- Extent Switches.......................: 769
- Avg. Pages per Extent..................: 6.2
- Scan Density [Best Count:Actual Count]......: 15.97% [123:770]
- Logical Scan Fragmentation ..............: 46.24%
- Extent Scan Fragmentation ...............: 18.24%
- Avg. Bytes Free per Page................: 3261.0
- Avg. Page Density (full)................: 59.71%
DBCC SHOWCONTIG scanning 'syspermissions' table...
Table: 'syspermissions' (9); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 4
- Extents Scanned.......................: 3
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.3
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 66.67%
- Avg. Bytes Free per Page................: 3771.5
- Avg. Page Density (full)................: 53.40%
DBCC SHOWCONTIG scanning 'sysdepends' table...
Table: 'sysdepends' (12); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 29
- Extents Scanned.......................: 8
- Extent Switches.......................: 26
- Avg. Pages per Extent..................: 3.6
- Scan Density [Best Count:Actual Count]......: 14.81% [4:27]
- Logical Scan Fragmentation ..............: 48.28%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 3096.8
- Avg. Page Density (full)................: 61.74%
DBCC SHOWCONTIG scanning 'sysxlogins' table...
Table: 'sysxlogins' (33); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 10
- Extents Scanned.......................: 4
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 2.5
- Scan Density [Best Count:Actual Count]......: 28.57% [2:7]
- Logical Scan Fragmentation ..............: 60.00%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 3522.2
- Avg. Page Density (full)................: 56.48%
DBCC SHOWCONTIG scanning 'sysmessages' table...
Table: 'sysmessages' (36); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 169
- Extents Scanned.......................: 23
- Extent Switches.......................: 26
- Avg. Pages per Extent..................: 7.3
- Scan Density [Best Count:Actual Count]......: 81.48% [22:27]
- Logical Scan Fragmentation ..............: 1.78%
- Extent Scan Fragmentation ...............: 13.04%
- Avg. Bytes Free per Page................: 3926.9
- Avg. Page Density (full)................: 51.48%
DBCC SHOWCONTIG scanning 'syslanguages' table...
Table: 'syslanguages' (44); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.5
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 33.33%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 3206.0
- Avg. Page Density (full)................: 60.39%
DBCC SHOWCONTIG scanning 'syscharsets' table...
Table: 'syscharsets' (45); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 33
- Extents Scanned.......................: 6
- Extent Switches.......................: 7
- Avg. Pages per Extent..................: 5.5
- Scan Density [Best Count:Actual Count]......: 62.50% [5:8]
- Logical Scan Fragmentation ..............: 24.24%
- Extent Scan Fragmentation ...............: 16.67%
- Avg. Bytes Free per Page................: 2896.6
- Avg. Page Density (full)................: 64.21%
DBCC SHOWCONTIG scanning 'sysaltfiles' table...
Table: 'sysaltfiles' (94); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 7
- Extents Scanned.......................: 4
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.8
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 14.29%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 4283.4
- Avg. Page Density (full)................: 47.08%
DBCC SHOWCONTIG scanning 'spt_values' table...
Table: 'spt_values' (85575343); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 7
- Extents Scanned.......................: 4
- Extent Switches.......................: 5
- Avg. Pages per Extent..................: 1.8
- Scan Density [Best Count:Actual Count]......: 16.67% [1:6]
- Logical Scan Fragmentation ..............: 28.57%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 2671.7
- Avg. Page Density (full)................: 66.99%|||You need not worry about fragmentation on System tables and use DBCC UPDATEUSAGE in order to correct the inconsistency.
I have an issue where my system tables are heavily fragmented. Currently our system creates between 200 and 400 new logins daily (its the method the application handles it security). However we have had execution times on this of between 13ms and 1900ms, very inconsistent.
I have seen in a previous thread http://dbforums.com/arch/70/2003/11/965415
that its Kalen Delaney's opinion that fragmentation doesn't really matter unless you have 'several dozen' pages
Here is an example of our sysxlogins table's report :
DBCC SHOWCONTIG scanning 'sysxlogins' table...
Table: 'sysxlogins' (33); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 162
- Extents Scanned.......................: 35
- Extent Switches.......................: 156
- Avg. Pages per Extent..................: 4.6
- Scan Density [Best Count:Actual Count]......: 13.38% [21:157]
- Logical Scan Fragmentation ..............: 49.38%
- Extent Scan Fragmentation ...............: 34.29%
- Avg. Bytes Free per Page................: 5694.6
- Avg. Page Density (full)................: 29.64%
As you can see, its got 162 pages, which probably counts as 'several' dozen.
I would like to know if its possible for us to defrag/reindex this table, as you can see, it has a VERY low Scan Density, and a rather high Fragmentation level.
Opinions, help, undocumented functionality all welcome
TiaanI am facing a same issue.
I have noticed that my system table are extremly fragmented. Take a look. Any suggestions? Or does it not matter that they are like that?
DBCC SHOWCONTIG scanning 'sysobjects' table...
Table: 'sysobjects' (1); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 23
- Extents Scanned.......................: 7
- Extent Switches.......................: 21
- Avg. Pages per Extent..................: 3.3
- Scan Density [Best Count:Actual Count]......: 13.64% [3:22]
- Logical Scan Fragmentation ..............: 43.48%
- Extent Scan Fragmentation ...............: 71.43%
- Avg. Bytes Free per Page................: 2744.6
- Avg. Page Density (full)................: 66.09%
DBCC SHOWCONTIG scanning 'sysindexes' table...
Table: 'sysindexes' (2); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 4
- Extents Scanned.......................: 4
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 25.00%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 3019.0
- Avg. Page Density (full)................: 62.70%
DBCC SHOWCONTIG scanning 'syscolumns' table...
Table: 'syscolumns' (3); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 85
- Extents Scanned.......................: 17
- Extent Switches.......................: 81
- Avg. Pages per Extent..................: 5.0
- Scan Density [Best Count:Actual Count]......: 13.41% [11:82]
- Logical Scan Fragmentation ..............: 44.71%
- Extent Scan Fragmentation ...............: 70.59%
- Avg. Bytes Free per Page................: 3348.5
- Avg. Page Density (full)................: 58.63%
DBCC SHOWCONTIG scanning 'syscomments' table...
Table: 'syscomments' (6); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 984
- Extents Scanned.......................: 159
- Extent Switches.......................: 769
- Avg. Pages per Extent..................: 6.2
- Scan Density [Best Count:Actual Count]......: 15.97% [123:770]
- Logical Scan Fragmentation ..............: 46.24%
- Extent Scan Fragmentation ...............: 18.24%
- Avg. Bytes Free per Page................: 3261.0
- Avg. Page Density (full)................: 59.71%
DBCC SHOWCONTIG scanning 'syspermissions' table...
Table: 'syspermissions' (9); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 4
- Extents Scanned.......................: 3
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.3
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 66.67%
- Avg. Bytes Free per Page................: 3771.5
- Avg. Page Density (full)................: 53.40%
DBCC SHOWCONTIG scanning 'sysdepends' table...
Table: 'sysdepends' (12); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 29
- Extents Scanned.......................: 8
- Extent Switches.......................: 26
- Avg. Pages per Extent..................: 3.6
- Scan Density [Best Count:Actual Count]......: 14.81% [4:27]
- Logical Scan Fragmentation ..............: 48.28%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 3096.8
- Avg. Page Density (full)................: 61.74%
DBCC SHOWCONTIG scanning 'sysxlogins' table...
Table: 'sysxlogins' (33); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 10
- Extents Scanned.......................: 4
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 2.5
- Scan Density [Best Count:Actual Count]......: 28.57% [2:7]
- Logical Scan Fragmentation ..............: 60.00%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 3522.2
- Avg. Page Density (full)................: 56.48%
DBCC SHOWCONTIG scanning 'sysmessages' table...
Table: 'sysmessages' (36); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 169
- Extents Scanned.......................: 23
- Extent Switches.......................: 26
- Avg. Pages per Extent..................: 7.3
- Scan Density [Best Count:Actual Count]......: 81.48% [22:27]
- Logical Scan Fragmentation ..............: 1.78%
- Extent Scan Fragmentation ...............: 13.04%
- Avg. Bytes Free per Page................: 3926.9
- Avg. Page Density (full)................: 51.48%
DBCC SHOWCONTIG scanning 'syslanguages' table...
Table: 'syslanguages' (44); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.5
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 33.33%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 3206.0
- Avg. Page Density (full)................: 60.39%
DBCC SHOWCONTIG scanning 'syscharsets' table...
Table: 'syscharsets' (45); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 33
- Extents Scanned.......................: 6
- Extent Switches.......................: 7
- Avg. Pages per Extent..................: 5.5
- Scan Density [Best Count:Actual Count]......: 62.50% [5:8]
- Logical Scan Fragmentation ..............: 24.24%
- Extent Scan Fragmentation ...............: 16.67%
- Avg. Bytes Free per Page................: 2896.6
- Avg. Page Density (full)................: 64.21%
DBCC SHOWCONTIG scanning 'sysaltfiles' table...
Table: 'sysaltfiles' (94); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 7
- Extents Scanned.......................: 4
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.8
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 14.29%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 4283.4
- Avg. Page Density (full)................: 47.08%
DBCC SHOWCONTIG scanning 'spt_values' table...
Table: 'spt_values' (85575343); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 7
- Extents Scanned.......................: 4
- Extent Switches.......................: 5
- Avg. Pages per Extent..................: 1.8
- Scan Density [Best Count:Actual Count]......: 16.67% [1:6]
- Logical Scan Fragmentation ..............: 28.57%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 2671.7
- Avg. Page Density (full)................: 66.99%|||You need not worry about fragmentation on System tables and use DBCC UPDATEUSAGE in order to correct the inconsistency.
Monday, March 12, 2012
optimal location for database files on SAN?
Hi, we have 2 sql servers that will have all their system databases/user
databases/log files located to a san. what is the best configuration?
note: sql1 performs transactional replication to sql2 (which is used for
reporting):
we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 5: sql1 user + system data files
if I could get access to another RAID 1 how would this sound:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 1: tempdb (from both sql1 and sql2)
RAID 5: sql1 user + system data files
Would both servers share the same tempdb? or would there be two instances?
since sql1 replicates to sql2, having all the logs on the same RAID 1 would
increase replication speed?
Any help most appreciated!
thanks, john
i will have access to initially 9 hdd's to build my config, but i might
possibly get a hold of more. any help most appreciated! ciao john
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>
|||I am not sure if I hit enter or lost the page. I tried an earlier response
Anyway, I suggest Mirroring your OS drive and a Binaries Drive witht the SAN
used for all the data files.
I like mirroring, but that is a bias others will contest.
Without knowing your SAN, I can't say much more. Some SANs don't give you
enough control to worry about RAID levels.
The real question is how many LUNs to the SAN?
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
>
|||The primary question for me is how many LUNs do you have.
I like Raid 0+1 (mirroring) rather than Raid-5 and all. Personal bias.
I would look to configure as follows:
Logical C: Mirror, OS files
Logical D: Mirror, Application (including SS) binaries
Logical E; SAN-data and log files.
This presumes 1 LUN, probably fiber, to the SAN.
As I said, I like mirroring. Without knowing which SAN you are using,
though, it is difficult to give advice (foot in mouth?) there. Some SANS do
not give you
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
>
|||I believe it is going to depend on the size of your databases and what they
are used for. Are they creating a lot of temp tables in the temp database?
if so, then temp needs two RAID1 volumes, 1 for data, 1 for logs.
Also, whatever config you end up with, separate the log files from the data
files.
Another thing to consider if you have it available is to use RAID1 or RAID10
on your databases. RAID5 is too expensive on the write operation for your
heavily used databases.
I hope this helps.
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>
databases/log files located to a san. what is the best configuration?
note: sql1 performs transactional replication to sql2 (which is used for
reporting):
we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 5: sql1 user + system data files
if I could get access to another RAID 1 how would this sound:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 1: tempdb (from both sql1 and sql2)
RAID 5: sql1 user + system data files
Would both servers share the same tempdb? or would there be two instances?
since sql1 replicates to sql2, having all the logs on the same RAID 1 would
increase replication speed?
Any help most appreciated!
thanks, john
i will have access to initially 9 hdd's to build my config, but i might
possibly get a hold of more. any help most appreciated! ciao john
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>
|||I am not sure if I hit enter or lost the page. I tried an earlier response
Anyway, I suggest Mirroring your OS drive and a Binaries Drive witht the SAN
used for all the data files.
I like mirroring, but that is a bias others will contest.
Without knowing your SAN, I can't say much more. Some SANs don't give you
enough control to worry about RAID levels.
The real question is how many LUNs to the SAN?
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
>
|||The primary question for me is how many LUNs do you have.
I like Raid 0+1 (mirroring) rather than Raid-5 and all. Personal bias.
I would look to configure as follows:
Logical C: Mirror, OS files
Logical D: Mirror, Application (including SS) binaries
Logical E; SAN-data and log files.
This presumes 1 LUN, probably fiber, to the SAN.
As I said, I like mirroring. Without knowing which SAN you are using,
though, it is difficult to give advice (foot in mouth?) there. Some SANS do
not give you
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
>
|||I believe it is going to depend on the size of your databases and what they
are used for. Are they creating a lot of temp tables in the temp database?
if so, then temp needs two RAID1 volumes, 1 for data, 1 for logs.
Also, whatever config you end up with, separate the log files from the data
files.
Another thing to consider if you have it available is to use RAID1 or RAID10
on your databases. RAID5 is too expensive on the write operation for your
heavily used databases.
I hope this helps.
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>
optimal location for database files on SAN?
Hi, we have 2 sql servers that will have all their system databases/user
databases/log files located to a san. what is the best configuration?
note: sql1 performs transactional replication to sql2 (which is used for
reporting):
we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 5: sql1 user + system data files
if I could get access to another RAID 1 how would this sound:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 1: tempdb (from both sql1 and sql2)
RAID 5: sql1 user + system data files
Would both servers share the same tempdb? or would there be two instances?
since sql1 replicates to sql2, having all the logs on the same RAID 1 would
increase replication speed?
Any help most appreciated!
thanks, johni will have access to initially 9 hdd's to build my config, but i might
possibly get a hold of more. any help most appreciated! ciao john
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>|||I am not sure if I hit enter or lost the page. I tried an earlier response
Anyway, I suggest Mirroring your OS drive and a Binaries Drive witht the SAN
used for all the data files.
I like mirroring, but that is a bias others will contest.
Without knowing your SAN, I can't say much more. Some SANs don't give you
enough control to worry about RAID levels.
The real question is how many LUNs to the SAN?
--
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> > Hi, we have 2 sql servers that will have all their system databases/user
> > databases/log files located to a san. what is the best configuration?
> >
> > note: sql1 performs transactional replication to sql2 (which is used for
> > reporting):
> >
> > we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > if I could get access to another RAID 1 how would this sound:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 1: tempdb (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > Would both servers share the same tempdb? or would there be two instances?
> >
> > since sql1 replicates to sql2, having all the logs on the same RAID 1
> > would increase replication speed?
> >
> > Any help most appreciated!
> > thanks, john
> >
>
>|||The primary question for me is how many LUNs do you have.
I like Raid 0+1 (mirroring) rather than Raid-5 and all. Personal bias.
I would look to configure as follows:
Logical C: Mirror, OS files
Logical D: Mirror, Application (including SS) binaries
Logical E; SAN-data and log files.
This presumes 1 LUN, probably fiber, to the SAN.
As I said, I like mirroring. Without knowing which SAN you are using,
though, it is difficult to give advice (foot in mouth?) there. Some SANS do
not give you
--
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> > Hi, we have 2 sql servers that will have all their system databases/user
> > databases/log files located to a san. what is the best configuration?
> >
> > note: sql1 performs transactional replication to sql2 (which is used for
> > reporting):
> >
> > we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > if I could get access to another RAID 1 how would this sound:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 1: tempdb (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > Would both servers share the same tempdb? or would there be two instances?
> >
> > since sql1 replicates to sql2, having all the logs on the same RAID 1
> > would increase replication speed?
> >
> > Any help most appreciated!
> > thanks, john
> >
>
>|||I believe it is going to depend on the size of your databases and what they
are used for. Are they creating a lot of temp tables in the temp database?
if so, then temp needs two RAID1 volumes, 1 for data, 1 for logs.
Also, whatever config you end up with, separate the log files from the data
files.
Another thing to consider if you have it available is to use RAID1 or RAID10
on your databases. RAID5 is too expensive on the write operation for your
heavily used databases.
I hope this helps.
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>
databases/log files located to a san. what is the best configuration?
note: sql1 performs transactional replication to sql2 (which is used for
reporting):
we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 5: sql1 user + system data files
if I could get access to another RAID 1 how would this sound:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 1: tempdb (from both sql1 and sql2)
RAID 5: sql1 user + system data files
Would both servers share the same tempdb? or would there be two instances?
since sql1 replicates to sql2, having all the logs on the same RAID 1 would
increase replication speed?
Any help most appreciated!
thanks, johni will have access to initially 9 hdd's to build my config, but i might
possibly get a hold of more. any help most appreciated! ciao john
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>|||I am not sure if I hit enter or lost the page. I tried an earlier response
Anyway, I suggest Mirroring your OS drive and a Binaries Drive witht the SAN
used for all the data files.
I like mirroring, but that is a bias others will contest.
Without knowing your SAN, I can't say much more. Some SANs don't give you
enough control to worry about RAID levels.
The real question is how many LUNs to the SAN?
--
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> > Hi, we have 2 sql servers that will have all their system databases/user
> > databases/log files located to a san. what is the best configuration?
> >
> > note: sql1 performs transactional replication to sql2 (which is used for
> > reporting):
> >
> > we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > if I could get access to another RAID 1 how would this sound:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 1: tempdb (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > Would both servers share the same tempdb? or would there be two instances?
> >
> > since sql1 replicates to sql2, having all the logs on the same RAID 1
> > would increase replication speed?
> >
> > Any help most appreciated!
> > thanks, john
> >
>
>|||The primary question for me is how many LUNs do you have.
I like Raid 0+1 (mirroring) rather than Raid-5 and all. Personal bias.
I would look to configure as follows:
Logical C: Mirror, OS files
Logical D: Mirror, Application (including SS) binaries
Logical E; SAN-data and log files.
This presumes 1 LUN, probably fiber, to the SAN.
As I said, I like mirroring. Without knowing which SAN you are using,
though, it is difficult to give advice (foot in mouth?) there. Some SANS do
not give you
--
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> > Hi, we have 2 sql servers that will have all their system databases/user
> > databases/log files located to a san. what is the best configuration?
> >
> > note: sql1 performs transactional replication to sql2 (which is used for
> > reporting):
> >
> > we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > if I could get access to another RAID 1 how would this sound:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 1: tempdb (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > Would both servers share the same tempdb? or would there be two instances?
> >
> > since sql1 replicates to sql2, having all the logs on the same RAID 1
> > would increase replication speed?
> >
> > Any help most appreciated!
> > thanks, john
> >
>
>|||I believe it is going to depend on the size of your databases and what they
are used for. Are they creating a lot of temp tables in the temp database?
if so, then temp needs two RAID1 volumes, 1 for data, 1 for logs.
Also, whatever config you end up with, separate the log files from the data
files.
Another thing to consider if you have it available is to use RAID1 or RAID10
on your databases. RAID5 is too expensive on the write operation for your
heavily used databases.
I hope this helps.
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>
optimal location for database files on SAN?
Hi, we have 2 sql servers that will have all their system databases/user
databases/log files located to a san. what is the best configuration?
note: sql1 performs transactional replication to sql2 (which is used for
reporting):
we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 5: sql1 user + system data files
if I could get access to another RAID 1 how would this sound:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 1: tempdb (from both sql1 and sql2)
RAID 5: sql1 user + system data files
Would both servers share the same tempdb? or would there be two instances?
since sql1 replicates to sql2, having all the logs on the same RAID 1 would
increase replication speed?
Any help most appreciated!
thanks, johni will have access to initially 9 hdd's to build my config, but i might
possibly get a hold of more. any help most appreciated! ciao john
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>|||I am not sure if I hit enter or lost the page. I tried an earlier response
Anyway, I suggest Mirroring your OS drive and a Binaries Drive witht the SAN
used for all the data files.
I like mirroring, but that is a bias others will contest.
Without knowing your SAN, I can't say much more. Some SANs don't give you
enough control to worry about RAID levels.
The real question is how many LUNs to the SAN?
--
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
>|||The primary question for me is how many LUNs do you have.
I like Raid 0+1 (mirroring) rather than Raid-5 and all. Personal bias.
I would look to configure as follows:
Logical C: Mirror, OS files
Logical D: Mirror, Application (including SS) binaries
Logical E; SAN-data and log files.
This presumes 1 LUN, probably fiber, to the SAN.
As I said, I like mirroring. Without knowing which SAN you are using,
though, it is difficult to give advice (foot in mouth?) there. Some SANS do
not give you
--
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
>|||I believe it is going to depend on the size of your databases and what they
are used for. Are they creating a lot of temp tables in the temp database?
if so, then temp needs two RAID1 volumes, 1 for data, 1 for logs.
Also, whatever config you end up with, separate the log files from the data
files.
Another thing to consider if you have it available is to use RAID1 or RAID10
on your databases. RAID5 is too expensive on the write operation for your
heavily used databases.
I hope this helps.
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>
databases/log files located to a san. what is the best configuration?
note: sql1 performs transactional replication to sql2 (which is used for
reporting):
we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 5: sql1 user + system data files
if I could get access to another RAID 1 how would this sound:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 1: tempdb (from both sql1 and sql2)
RAID 5: sql1 user + system data files
Would both servers share the same tempdb? or would there be two instances?
since sql1 replicates to sql2, having all the logs on the same RAID 1 would
increase replication speed?
Any help most appreciated!
thanks, johni will have access to initially 9 hdd's to build my config, but i might
possibly get a hold of more. any help most appreciated! ciao john
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>|||I am not sure if I hit enter or lost the page. I tried an earlier response
Anyway, I suggest Mirroring your OS drive and a Binaries Drive witht the SAN
used for all the data files.
I like mirroring, but that is a bias others will contest.
Without knowing your SAN, I can't say much more. Some SANs don't give you
enough control to worry about RAID levels.
The real question is how many LUNs to the SAN?
--
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
>|||The primary question for me is how many LUNs do you have.
I like Raid 0+1 (mirroring) rather than Raid-5 and all. Personal bias.
I would look to configure as follows:
Logical C: Mirror, OS files
Logical D: Mirror, Application (including SS) binaries
Logical E; SAN-data and log files.
This presumes 1 LUN, probably fiber, to the SAN.
As I said, I like mirroring. Without knowing which SAN you are using,
though, it is difficult to give advice (foot in mouth?) there. Some SANS do
not give you
--
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
>|||I believe it is going to depend on the size of your databases and what they
are used for. Are they creating a lot of temp tables in the temp database?
if so, then temp needs two RAID1 volumes, 1 for data, 1 for logs.
Also, whatever config you end up with, separate the log files from the data
files.
Another thing to consider if you have it available is to use RAID1 or RAID10
on your databases. RAID5 is too expensive on the write operation for your
heavily used databases.
I hope this helps.
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>
optimal database configure
hi,
I use sqlw2k sp3 server. I have configure 2xRAID1 with 4 ucp and 8GB
RAM. The first RAID1 for operation system and binary sql and the secend
RAID1 for data. I want to use one instance for two independent database.
Both database will have about 10-20GB data.
I am not sure where put the transation log (on which RAID the first or
the secend). This is my one question.
And my secend question concerning filegroup. Do you have any recommend
to the optimal of this?
rgs,
sylwester
--
Posted via http://dbforums.comIf you only have two RAID1 arrays you might be better off placing the =OS, binaries, data files on one array and the log files on the other =array.
a better setup would involve three raid sets
RAID set: OS + binaries + tempdb + system databases
RAID set: data files
RAID set: log files
The level of RAID that you choose should depend on your desired level of =performance and redundancy.
-- Keith
"skosci" <skoscielecky@.hotmail.com> wrote in message =news:3334411.1062764290@.dbforums.com...
> > hi,
> > > > I use sqlw2k sp3 server. I have configure 2xRAID1 with 4 ucp and 8GB
> RAM. The first RAID1 for operation system and binary sql and the =secend
> RAID1 for data. I want to use one instance for two independent =database.
> Both database will have about 10-20GB data.
> > > > I am not sure where put the transation log (on which RAID the first or
> the secend). This is my one question.
> > > > > > And my secend question concerning filegroup. Do you have any recommend
> to the optimal of this?
> > > > rgs,
> > sylwester
> > > --
> Posted via http://dbforums.com|||Hi Keith,
So I supposed like you. I cannot change configure on the level
RAID just now.
On the one RAID1 installed OS with 33GB.
The secend RAID1 has got 66GB.
Real size of both database is 12GB. In your first recommend I should
reinstall OS.
Tell me more what do you think if I setup like below:
first RAID set: OS+binaries+tempdb+systemdatabase and
secend RAID set: data files and log files.
How much it's worse from your first recommend?
Regards,
Sylwester
Posted via http://dbforums.com
I use sqlw2k sp3 server. I have configure 2xRAID1 with 4 ucp and 8GB
RAM. The first RAID1 for operation system and binary sql and the secend
RAID1 for data. I want to use one instance for two independent database.
Both database will have about 10-20GB data.
I am not sure where put the transation log (on which RAID the first or
the secend). This is my one question.
And my secend question concerning filegroup. Do you have any recommend
to the optimal of this?
rgs,
sylwester
--
Posted via http://dbforums.comIf you only have two RAID1 arrays you might be better off placing the =OS, binaries, data files on one array and the log files on the other =array.
a better setup would involve three raid sets
RAID set: OS + binaries + tempdb + system databases
RAID set: data files
RAID set: log files
The level of RAID that you choose should depend on your desired level of =performance and redundancy.
-- Keith
"skosci" <skoscielecky@.hotmail.com> wrote in message =news:3334411.1062764290@.dbforums.com...
> > hi,
> > > > I use sqlw2k sp3 server. I have configure 2xRAID1 with 4 ucp and 8GB
> RAM. The first RAID1 for operation system and binary sql and the =secend
> RAID1 for data. I want to use one instance for two independent =database.
> Both database will have about 10-20GB data.
> > > > I am not sure where put the transation log (on which RAID the first or
> the secend). This is my one question.
> > > > > > And my secend question concerning filegroup. Do you have any recommend
> to the optimal of this?
> > > > rgs,
> > sylwester
> > > --
> Posted via http://dbforums.com|||Hi Keith,
So I supposed like you. I cannot change configure on the level
RAID just now.
On the one RAID1 installed OS with 33GB.
The secend RAID1 has got 66GB.
Real size of both database is 12GB. In your first recommend I should
reinstall OS.
Tell me more what do you think if I setup like below:
first RAID set: OS+binaries+tempdb+systemdatabase and
secend RAID set: data files and log files.
How much it's worse from your first recommend?
Regards,
Sylwester
Posted via http://dbforums.com
Wednesday, March 7, 2012
operationg system requirement
I am in the process of learning SQL from scratch and figure it would be helpful if I can run a local server and experiment with it as I move along some book reading. I have a few questions regarding the operating system requirement for running a localhost server and hope some of you can answer to my novice questions. Can I setup a server under Windows XP? when I looked thru the documentation it indicated only windows 2000 and other Microsoft server OS will support a SQL server setup. Why then it also indicate XP as compatible? Is it because it's compatible as a client? If I do have to run a server under a different operationg system, what is the best approach for setting up another server on my only PC system along with my XP. Thanks for any answers and comments in advance.
Does "Windows XP with SP 2 or later" mean that the media center edition 2005 should suffice?
|||
|||so with an athlon 64 X2 dual Core processor 3800+ 2Ghz I should be ordering the
x64 developer version rather than win32? Or would the latter also work (and be compatible with an older processor)
|||
Are you talking about SQL 2005? XP does support SQL 2005, but it depends on which edition you have. See the following link for more information about which edition is supported on which OS. http://www.microsoft.com/sql/prodinfo/sysreqs/default.mspx
|||I'm not completely clear on whether I'm covered for a developer editionDoes "Windows XP with SP 2 or later" mean that the media center edition 2005 should suffice?
|||
Short answer: Yes.
Please see topic "Hardware and Software Requirementse for Installing SQL Server 2005" for more information.
http://msdn2.microsoft.com/en-us/library/ms143506(SQL.90).aspx
|||Short Answer: Thanks|||so with an athlon 64 X2 dual Core processor 3800+ 2Ghz I should be ordering the
x64 developer version rather than win32? Or would the latter also work (and be compatible with an older processor)
|||
x86 can install on X64 machine, but you should install x64 version if you can.
Operationg System Command (CmdExec) job failing
I have sql 2000 job that has been setup as a "Operating System Command (CmdExec) job. I am logged into the SQL Server as DomainName\SQLAdmin this domain account is part of the Administrator group on the SQL Server that the job in running on. This account has SysAdmin rights and is also starting the MSSQLSERVER Service and SQLSERVERAGENT Service on the same machine.
The job just copies files from one directory to another, here's the code.
D:
cd \MSSQL\BACKUP\AP\AP_Primary
xcopy *.* D:\MSSQL\BACKUP\AP\ /s/y/d
When run as a job, with the owner of the job being DomainName\SQLAdmin the job fails with the following error message: Executed as user: DomainName\SQLAdmin. The process could not be created for step 1 of job 0x822E9AD29DCAAF4196369A46C7FE212A (reason: Access is denied). The step failed.
Here's the weird part if I open a command window on the sql server and run the batch it works fine.
I even tried executing the commands via xp_cmdshell but that didn't work either, I recieved the message: (1 row(s) affected) with the output being NULL and the file was never copied.
Anyone have an idea whats going on?It sounds like there could be multiple reasons for the access denied issue, but an odd one which I just found out about follows:
I was just trying to restore a db and I was receiving an Access denied error. I ended up finding out that some of the parent folders were in 'read only' mode. After I disabled it, everything worked fine.
Good luck,
Hope this helped|||what account is listed as the job owner?|||The account that owns the job, DomainName\SQLAdmin, is also the account that belongs to the Administrator group on the server and Stops and Starts the MSSQLSERVER/SQLSERVERAGENT services. As far as I can tell this account has all the permissions possible.
Also checked permissions on all parent folders none are marked read-only.|||I found a work around but it doesn't explain why my original job failed. What I did was create a batch file with the following code.
D:
cd \MSSQL\BACKUP\AP\AP_Primary
xcopy *.* D:\MSSQL\BACKUP\AP\ /s/y/d
I then stored it on the C: drive and called the batch from within the job and this worked. So anyone have an idea what's going on? I'm sure it's a permissions thing but I can't explain it.
The job just copies files from one directory to another, here's the code.
D:
cd \MSSQL\BACKUP\AP\AP_Primary
xcopy *.* D:\MSSQL\BACKUP\AP\ /s/y/d
When run as a job, with the owner of the job being DomainName\SQLAdmin the job fails with the following error message: Executed as user: DomainName\SQLAdmin. The process could not be created for step 1 of job 0x822E9AD29DCAAF4196369A46C7FE212A (reason: Access is denied). The step failed.
Here's the weird part if I open a command window on the sql server and run the batch it works fine.
I even tried executing the commands via xp_cmdshell but that didn't work either, I recieved the message: (1 row(s) affected) with the output being NULL and the file was never copied.
Anyone have an idea whats going on?It sounds like there could be multiple reasons for the access denied issue, but an odd one which I just found out about follows:
I was just trying to restore a db and I was receiving an Access denied error. I ended up finding out that some of the parent folders were in 'read only' mode. After I disabled it, everything worked fine.
Good luck,
Hope this helped|||what account is listed as the job owner?|||The account that owns the job, DomainName\SQLAdmin, is also the account that belongs to the Administrator group on the server and Stops and Starts the MSSQLSERVER/SQLSERVERAGENT services. As far as I can tell this account has all the permissions possible.
Also checked permissions on all parent folders none are marked read-only.|||I found a work around but it doesn't explain why my original job failed. What I did was create a batch file with the following code.
D:
cd \MSSQL\BACKUP\AP\AP_Primary
xcopy *.* D:\MSSQL\BACKUP\AP\ /s/y/d
I then stored it on the C: drive and called the batch from within the job and this worked. So anyone have an idea what's going on? I'm sure it's a permissions thing but I can't explain it.
Operating System Upgrade
Hi,
We are running SQL Server 7 on NT Server.
I am responsible for an upgrade path which will migrate to Windows 2000 Server and SQL Server 2000.
The upgrade will take place in steps with the operating system done first.
Is there any documentation describing the recommended methodology for performing such an upgrade ?
Are there any known problems to watch out for ?
I know theses are general questions but I would rather be aware of best prctices or potential problems before I start.
Any help gratefully received,
Den.
Have a look at
http://www.sqlteam.com/item.asp?ItemID=9066
http://www.sqlteam.com/item.asp?ItemID=9465
Query plans will change so some SQL may go faster others slower.
Paul
"Den" <anonymous@.discussions.microsoft.com> wrote in message
news:4CB242DE-4818-4E1E-AC3C-21D962AADDDC@.microsoft.com...
> Hi,
> We are running SQL Server 7 on NT Server.
> I am responsible for an upgrade path which will migrate to Windows 2000
Server and SQL Server 2000.
> The upgrade will take place in steps with the operating system done first.
> Is there any documentation describing the recommended methodology for
performing such an upgrade ?
> Are there any known problems to watch out for ?
> I know theses are general questions but I would rather be aware of best
prctices or potential problems before I start.
> Any help gratefully received,
> Den.
We are running SQL Server 7 on NT Server.
I am responsible for an upgrade path which will migrate to Windows 2000 Server and SQL Server 2000.
The upgrade will take place in steps with the operating system done first.
Is there any documentation describing the recommended methodology for performing such an upgrade ?
Are there any known problems to watch out for ?
I know theses are general questions but I would rather be aware of best prctices or potential problems before I start.
Any help gratefully received,
Den.
Have a look at
http://www.sqlteam.com/item.asp?ItemID=9066
http://www.sqlteam.com/item.asp?ItemID=9465
Query plans will change so some SQL may go faster others slower.
Paul
"Den" <anonymous@.discussions.microsoft.com> wrote in message
news:4CB242DE-4818-4E1E-AC3C-21D962AADDDC@.microsoft.com...
> Hi,
> We are running SQL Server 7 on NT Server.
> I am responsible for an upgrade path which will migrate to Windows 2000
Server and SQL Server 2000.
> The upgrade will take place in steps with the operating system done first.
> Is there any documentation describing the recommended methodology for
performing such an upgrade ?
> Are there any known problems to watch out for ?
> I know theses are general questions but I would rather be aware of best
prctices or potential problems before I start.
> Any help gratefully received,
> Den.
Operating System Upgrade
Hi,
We are running SQL Server 7 on NT Server.
I am responsible for an upgrade path which will migrate to Windows 2000 Serv
er and SQL Server 2000.
The upgrade will take place in steps with the operating system done first.
Is there any documentation describing the recommended methodology for perfor
ming such an upgrade ?
Are there any known problems to watch out for ?
I know theses are general questions but I would rather be aware of best prct
ices or potential problems before I start.
Any help gratefully received,
Den.Have a look at
http://www.sqlteam.com/item.asp?ItemID=9066
http://www.sqlteam.com/item.asp?ItemID=9465
Query plans will change so some SQL may go faster others slower.
Paul
"Den" <anonymous@.discussions.microsoft.com> wrote in message
news:4CB242DE-4818-4E1E-AC3C-21D962AADDDC@.microsoft.com...
> Hi,
> We are running SQL Server 7 on NT Server.
> I am responsible for an upgrade path which will migrate to Windows 2000
Server and SQL Server 2000.
> The upgrade will take place in steps with the operating system done first.
> Is there any documentation describing the recommended methodology for
performing such an upgrade ?
> Are there any known problems to watch out for ?
> I know theses are general questions but I would rather be aware of best
prctices or potential problems before I start.
> Any help gratefully received,
> Den.
We are running SQL Server 7 on NT Server.
I am responsible for an upgrade path which will migrate to Windows 2000 Serv
er and SQL Server 2000.
The upgrade will take place in steps with the operating system done first.
Is there any documentation describing the recommended methodology for perfor
ming such an upgrade ?
Are there any known problems to watch out for ?
I know theses are general questions but I would rather be aware of best prct
ices or potential problems before I start.
Any help gratefully received,
Den.Have a look at
http://www.sqlteam.com/item.asp?ItemID=9066
http://www.sqlteam.com/item.asp?ItemID=9465
Query plans will change so some SQL may go faster others slower.
Paul
"Den" <anonymous@.discussions.microsoft.com> wrote in message
news:4CB242DE-4818-4E1E-AC3C-21D962AADDDC@.microsoft.com...
> Hi,
> We are running SQL Server 7 on NT Server.
> I am responsible for an upgrade path which will migrate to Windows 2000
Server and SQL Server 2000.
> The upgrade will take place in steps with the operating system done first.
> Is there any documentation describing the recommended methodology for
performing such an upgrade ?
> Are there any known problems to watch out for ?
> I know theses are general questions but I would rather be aware of best
prctices or potential problems before I start.
> Any help gratefully received,
> Den.
Operating System Upgrade
Hi
We are running SQL Server 7 on NT Server
I am responsible for an upgrade path which will migrate to Windows 2000 Server and SQL Server 2000
The upgrade will take place in steps with the operating system done first
Is there any documentation describing the recommended methodology for performing such an upgrade
Are there any known problems to watch out for
I know theses are general questions but I would rather be aware of best prctices or potential problems before I start
Any help gratefully received
Den.Have a look at
http://www.sqlteam.com/item.asp?ItemID=9066
http://www.sqlteam.com/item.asp?ItemID=9465
Query plans will change so some SQL may go faster others slower.
Paul
"Den" <anonymous@.discussions.microsoft.com> wrote in message
news:4CB242DE-4818-4E1E-AC3C-21D962AADDDC@.microsoft.com...
> Hi,
> We are running SQL Server 7 on NT Server.
> I am responsible for an upgrade path which will migrate to Windows 2000
Server and SQL Server 2000.
> The upgrade will take place in steps with the operating system done first.
> Is there any documentation describing the recommended methodology for
performing such an upgrade ?
> Are there any known problems to watch out for ?
> I know theses are general questions but I would rather be aware of best
prctices or potential problems before I start.
> Any help gratefully received,
> Den.
We are running SQL Server 7 on NT Server
I am responsible for an upgrade path which will migrate to Windows 2000 Server and SQL Server 2000
The upgrade will take place in steps with the operating system done first
Is there any documentation describing the recommended methodology for performing such an upgrade
Are there any known problems to watch out for
I know theses are general questions but I would rather be aware of best prctices or potential problems before I start
Any help gratefully received
Den.Have a look at
http://www.sqlteam.com/item.asp?ItemID=9066
http://www.sqlteam.com/item.asp?ItemID=9465
Query plans will change so some SQL may go faster others slower.
Paul
"Den" <anonymous@.discussions.microsoft.com> wrote in message
news:4CB242DE-4818-4E1E-AC3C-21D962AADDDC@.microsoft.com...
> Hi,
> We are running SQL Server 7 on NT Server.
> I am responsible for an upgrade path which will migrate to Windows 2000
Server and SQL Server 2000.
> The upgrade will take place in steps with the operating system done first.
> Is there any documentation describing the recommended methodology for
performing such an upgrade ?
> Are there any known problems to watch out for ?
> I know theses are general questions but I would rather be aware of best
prctices or potential problems before I start.
> Any help gratefully received,
> Den.
Operating system question
Hello
Can I install SQL Server 2000 on Windows 2003 Server Enterprise?
Best Regards
Wojciech Znaniecki
Take a look here:
http://www.microsoft.com/sql/howtobu...netsupport.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Wojtek Z" <wojtas_z@.poczta.fm> schrieb im Newsbeitrag
news:d3vqvl$qqf$1@.nemesis.news.tpi.pl...
> Hello
> Can I install SQL Server 2000 on Windows 2003 Server Enterprise?
> --
> Best Regards
> Wojciech Znaniecki
>
|||Hi
Yes, just make sure that you apply SP3a after the installation otherwise SQL
Server will not work on that platform.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Wojtek Z" <wojtas_z@.poczta.fm> wrote in message
news:d3vqvl$qqf$1@.nemesis.news.tpi.pl...
> Hello
> Can I install SQL Server 2000 on Windows 2003 Server Enterprise?
> --
> Best Regards
> Wojciech Znaniecki
>
Can I install SQL Server 2000 on Windows 2003 Server Enterprise?
Best Regards
Wojciech Znaniecki
Take a look here:
http://www.microsoft.com/sql/howtobu...netsupport.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Wojtek Z" <wojtas_z@.poczta.fm> schrieb im Newsbeitrag
news:d3vqvl$qqf$1@.nemesis.news.tpi.pl...
> Hello
> Can I install SQL Server 2000 on Windows 2003 Server Enterprise?
> --
> Best Regards
> Wojciech Znaniecki
>
|||Hi
Yes, just make sure that you apply SP3a after the installation otherwise SQL
Server will not work on that platform.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Wojtek Z" <wojtas_z@.poczta.fm> wrote in message
news:d3vqvl$qqf$1@.nemesis.news.tpi.pl...
> Hello
> Can I install SQL Server 2000 on Windows 2003 Server Enterprise?
> --
> Best Regards
> Wojciech Znaniecki
>
Operating system question
Hello
Can I install SQL Server 2000 on Windows 2003 Server Enterprise?
--
Best Regards
Wojciech ZnanieckiTake a look here:
http://www.microsoft.com/sql/howtobuy/windowsnetsupport.asp
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Wojtek Z" <wojtas_z@.poczta.fm> schrieb im Newsbeitrag
news:d3vqvl$qqf$1@.nemesis.news.tpi.pl...
> Hello
> Can I install SQL Server 2000 on Windows 2003 Server Enterprise?
> --
> Best Regards
> Wojciech Znaniecki
>|||Hi
Yes, just make sure that you apply SP3a after the installation otherwise SQL
Server will not work on that platform.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Wojtek Z" <wojtas_z@.poczta.fm> wrote in message
news:d3vqvl$qqf$1@.nemesis.news.tpi.pl...
> Hello
> Can I install SQL Server 2000 on Windows 2003 Server Enterprise?
> --
> Best Regards
> Wojciech Znaniecki
>
Can I install SQL Server 2000 on Windows 2003 Server Enterprise?
--
Best Regards
Wojciech ZnanieckiTake a look here:
http://www.microsoft.com/sql/howtobuy/windowsnetsupport.asp
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Wojtek Z" <wojtas_z@.poczta.fm> schrieb im Newsbeitrag
news:d3vqvl$qqf$1@.nemesis.news.tpi.pl...
> Hello
> Can I install SQL Server 2000 on Windows 2003 Server Enterprise?
> --
> Best Regards
> Wojciech Znaniecki
>|||Hi
Yes, just make sure that you apply SP3a after the installation otherwise SQL
Server will not work on that platform.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Wojtek Z" <wojtas_z@.poczta.fm> wrote in message
news:d3vqvl$qqf$1@.nemesis.news.tpi.pl...
> Hello
> Can I install SQL Server 2000 on Windows 2003 Server Enterprise?
> --
> Best Regards
> Wojciech Znaniecki
>
Operating system question
Hello
Can I install SQL Server 2000 on Windows 2003 Server Enterprise?
Best Regards
Wojciech ZnanieckiTake a look here:
http://www.microsoft.com/sql/howtob...snetsupport.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Wojtek Z" <wojtas_z@.poczta.fm> schrieb im Newsbeitrag
news:d3vqvl$qqf$1@.nemesis.news.tpi.pl...
> Hello
> Can I install SQL Server 2000 on Windows 2003 Server Enterprise?
> --
> Best Regards
> Wojciech Znaniecki
>|||Hi
Yes, just make sure that you apply SP3a after the installation otherwise SQL
Server will not work on that platform.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Wojtek Z" <wojtas_z@.poczta.fm> wrote in message
news:d3vqvl$qqf$1@.nemesis.news.tpi.pl...
> Hello
> Can I install SQL Server 2000 on Windows 2003 Server Enterprise?
> --
> Best Regards
> Wojciech Znaniecki
>
Can I install SQL Server 2000 on Windows 2003 Server Enterprise?
Best Regards
Wojciech ZnanieckiTake a look here:
http://www.microsoft.com/sql/howtob...snetsupport.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Wojtek Z" <wojtas_z@.poczta.fm> schrieb im Newsbeitrag
news:d3vqvl$qqf$1@.nemesis.news.tpi.pl...
> Hello
> Can I install SQL Server 2000 on Windows 2003 Server Enterprise?
> --
> Best Regards
> Wojciech Znaniecki
>|||Hi
Yes, just make sure that you apply SP3a after the installation otherwise SQL
Server will not work on that platform.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Wojtek Z" <wojtas_z@.poczta.fm> wrote in message
news:d3vqvl$qqf$1@.nemesis.news.tpi.pl...
> Hello
> Can I install SQL Server 2000 on Windows 2003 Server Enterprise?
> --
> Best Regards
> Wojciech Znaniecki
>
Saturday, February 25, 2012
Operating System Memory. How much?
I currently am looking for a document from microsoft (or close to it)
that tells me that I should have X amount of memory set aside for the
OS for 16 and/or 32 gig memory configurations.
This is also one of those "my boss is asking me for a docuemnt to
support what I'm telling him. things"
this is in ref to SQL/AWE.
On Oct 5, 9:00 am, shawncr...@.yahoo.com wrote:
> I currently am looking for a document from microsoft (or close to it)
> that tells me that I should have X amount of memory set aside for the
> OS for 16 and/or 32 gig memory configurations.
> This is also one of those "my boss is asking me for a docuemnt to
> support what I'm telling him. things"
that tells me that I should have X amount of memory set aside for the
OS for 16 and/or 32 gig memory configurations.
This is also one of those "my boss is asking me for a docuemnt to
support what I'm telling him. things"
this is in ref to SQL/AWE.
On Oct 5, 9:00 am, shawncr...@.yahoo.com wrote:
> I currently am looking for a document from microsoft (or close to it)
> that tells me that I should have X amount of memory set aside for the
> OS for 16 and/or 32 gig memory configurations.
> This is also one of those "my boss is asking me for a docuemnt to
> support what I'm telling him. things"
Operating System Memory. How much?
I currently am looking for a document from microsoft (or close to it)
that tells me that I should have X amount of memory set aside for the
OS for 16 and/or 32 gig memory configurations.
This is also one of those "my boss is asking me for a docuemnt to
support what I'm telling him. things"this is in ref to SQL/AWE.
On Oct 5, 9:00 am, shawncr...@.yahoo.com wrote:
> I currently am looking for a document from microsoft (or close to it)
> that tells me that I should have X amount of memory set aside for the
> OS for 16 and/or 32 gig memory configurations.
> This is also one of those "my boss is asking me for a docuemnt to
> support what I'm telling him. things"
that tells me that I should have X amount of memory set aside for the
OS for 16 and/or 32 gig memory configurations.
This is also one of those "my boss is asking me for a docuemnt to
support what I'm telling him. things"this is in ref to SQL/AWE.
On Oct 5, 9:00 am, shawncr...@.yahoo.com wrote:
> I currently am looking for a document from microsoft (or close to it)
> that tells me that I should have X amount of memory set aside for the
> OS for 16 and/or 32 gig memory configurations.
> This is also one of those "my boss is asking me for a docuemnt to
> support what I'm telling him. things"
Operating System Memory. How much?
I currently am looking for a document from microsoft (or close to it)
that tells me that I should have X amount of memory set aside for the
OS for 16 and/or 32 gig memory configurations.
This is also one of those "my boss is asking me for a docuemnt to
support what I'm telling him. things"this is in ref to SQL/AWE.
On Oct 5, 9:00 am, shawncr...@.yahoo.com wrote:
> I currently am looking for a document from microsoft (or close to it)
> that tells me that I should have X amount of memory set aside for the
> OS for 16 and/or 32 gig memory configurations.
> This is also one of those "my boss is asking me for a docuemnt to
> support what I'm telling him. things"
that tells me that I should have X amount of memory set aside for the
OS for 16 and/or 32 gig memory configurations.
This is also one of those "my boss is asking me for a docuemnt to
support what I'm telling him. things"this is in ref to SQL/AWE.
On Oct 5, 9:00 am, shawncr...@.yahoo.com wrote:
> I currently am looking for a document from microsoft (or close to it)
> that tells me that I should have X amount of memory set aside for the
> OS for 16 and/or 32 gig memory configurations.
> This is also one of those "my boss is asking me for a docuemnt to
> support what I'm telling him. things"
Subscribe to:
Posts (Atom)