Passing non-URL Friendly Parameters?

I have a parameter in my SQL report which is a system account number. This
account number often contains non-URL friendly characters (i.e. '<', '>',
';', ' ' , etc...). How can I create some type of substitition function to
work around this issue?
I reall need to pass the parameter through the URL. Thank you in advance.
- MaxWhat instead of using the GET method, you use the POST method to send your
parameters
Med Bouchenafa
"Max Tyack" <MaxTyack@.discussions.microsoft.com> a écrit dans le message de
news: D3AC72E4-77AE-4410-91E3-DC2DAB367842@.microsoft.com...
>I have a parameter in my SQL report which is a system account number. This
> account number often contains non-URL friendly characters (i.e. '<', '>',
> ';', ' ' , etc...). How can I create some type of substitition function to
> work around this issue?
> I reall need to pass the parameter through the URL. Thank you in advance.
> - Max|||Do you need the link on a static page or from an other report?
Reporting Services should recognize escaped uri parameters. So instead of
'http://Hello World' you can use 'http://Hello%20World'. The same applies to
<, > and so on. The % values are the hex-ascii values, so you might need to
look up an ascii table and do a string replace ( like strUri.Replace(" ",
"%20") )
In .NET 2.0 you should look up for the Uri class and use EscapeUriString or
EscapeDataString.
I hope it's understandable, else you can try it at
http://www.greymana.net/examples/escape.html . For Non-URI friendly
parameters the Dest (param) might be more intersting. It's all in client-side
javascript.
"Max Tyack" wrote:
> I have a parameter in my SQL report which is a system account number. This
> account number often contains non-URL friendly characters (i.e. '<', '>',
> ';', ' ' , etc...). How can I create some type of substitition function to
> work around this issue?
> I reall need to pass the parameter through the URL. Thank you in advance.
> - Max

passing a timestamp datatype column to a variable and back

After several hours of trying, I trow the towel in the ring and come here to ask a question.

Source system uses a timestamp column in the transaction tables. which is equal to a non-nullable binary(8) datatype (sql 2000 bol).

What I want to do is get the timestamp at the start of the transfer and at the end of the transfer of data. and store these in a controltable

I try to do this in 2 sql execute tasks:

sqltask 1: "select @.@.DBTS AS SourceTimestamp" and map the resultset to a variable. Here come's the first problem what variable type to take ?

DBNULL works (meaning it doesn't give errors) (BTW: is there a way to put a variable as a watch when debugging sql tasks ?)

INT64 and UINT64 don't work error message that types for column and parameter are different

STRING works

Then I want to store this variable back in a table of a different data source

sqltask2: "insert into controltable values(getdate(), ?)" and make an input parameter that takes the previous timestamp ...

if I took DBNULL as a type for the variable there doesn't seem to be a single parameter type that works ?

if i take STRING as a type for the variable I have to modify the sql to do the explicit conversion from string to binary so I change CAST(? as binary). It doesn't return any error but the value stored in the table is 0x00000000000 and not the actual timestamp.

Any help on this one ? Why are the INT64/Bigint not working here, you can perfectly do a convert(bigint, timestampfield) in sql ?

How came the SQL datatypes, and the variable datatypes, parameter datatypes are so badly alligned to each other (and all seem to use different names) ?

tx for any help

Dirk

After some more hours (It just kept anoying me till late in the evening) I finaly found a way to make this work.

Make the variable in SSIS of type string

To store a timestamp as a variable

select Cast(timestampcolumn as bigint) as outputcolumn from controltable

create result set to map outputcolumn to variable

(SSIS wil do the conversion to string itself) Know that I tried to include this in the sql a convert to bigint an then to string but didn't work.

To use the timestamp in your queries or write it to another table

select * from table where timestampcolumn >= CAST(? AS BIGINT)

parameter mapping variable input type VARCHAR

(SSIS wil to the conversion from bigint to binary(8) itself

Got this working.

Point stays that datatypes should be more alligned between all the different places we use them, scripts, expressions, variables, parameters, sql data types...

Anyone got a better way, let me know.

tx

Dirk

|||

SSIS can genertae timestamps without an external data source. Is that not an option for you?

-Jamie

|||

Hi Jamie,

Don't think so. Let me explain what the purpose of all this was. The source ERP system uses timestamp columns on the tables. So I would like to use these columns as ModificationTime for my incremental load of new data in the DWH.

My problem was that I needed to get the @.@.DBTS from the source ERP system (hence the first SQL task) so that I would know the timestamp from the moment the upload started (my datapumps have a where clause like WHERE ModificationTimestamp >= TimestampOfStartLastSuccesfullUpload, so it will get all new and changed records since the start of the last upload).

Then I needed to store this Timestamp in my control table of the DWH and that's the 2d SQLtask.

I would have been extremly simple if the controltable was in the source ERP system, I could just insert the @.@.DBTS, but that was not an option. So I needed to pass the timestamp between the 2 SQL tasks. And that was my problem, getting the timestamp from the first select SQL task store it in a variable and pass that variable to the second insert sql task.

Perhaps this would have been easier in a script task, but I'm not that good at cooding. I haven't passed the level of copy, paste and modify some code ;-).

What do you think ? Is there a simpler solution ?

Dirk

|||

Ah I understand now. You need to persist the max timestamp between executions. That's a common requirement of course.

I may be mistaken but it seems the format of the timestamp is somewhat proprietary, hence the solution that you have come up with should be the most suitable - it sounds as though it will work fine though.

Question. What does the timestamp value look like? i.e. Can you paste it up here?

Regards

Jamie

|||

Dirk Van der Straeten wrote:

(BTW: is there a way to put a variable as a watch when debugging sql tasks ?)

Yes, there is. Drag the variable into a Watch window within the BIDS environment. When you execute and break you will be able to look at the value of the variable.

-Jamie

|||

Jamie,

Following select on the database and resultset.

select @.@.dbts as timestamp, CAST(@.@.dbts as bigint) as ConvertedInteger, CAST(@.@.dbts as varchar) as ConvertedVarchar

0x00000000000F94C7 1021127 _

The Timestamp is a binary(8) that is non nullable. But you can also interpreted is a an 8 byte integer which is the same as a bigint. Where __ is actualy the blanc of the string. That was the problem that converting directly from timestamp into the string variable did not work. Strangly I also didn't get it to work when I made the Variable a UI64 or I64 and then user LARGE_INTEGER as parameter type. He gave the error that the types where not compatible.

Got the watch thing figured out now.Tx.

/Dirk

passing a new query to a report

hi all,

ok, here is my problem. my employer has tasked me to create fairly complex program.. and with it he wants a reporting system. now, i have been given permission to use VB Express, SQL Server 2005 Express with Advanced Features, and the SQL Server Express Toolkit for my application. my employer would prefer not to have to spend the money on visual studio professional (although, in truth it would make my life so much easier as reports are integrated into the IDE, via crystalreports), so i HAVE to do it the way of viewing all reports through ie.

now (after much toil) i have finally figured out how to display the reports in an internet explorer window (which i can also run from my application), and the report displays no problem! when i finally managed this i was through the bloody moon. now, what i would like to do is one of two things...

1) display a report based on a dynamic query from my vb.net form, so i can filter the results how i want

or

2) simply define a static query from within the report, create a couple of parameters in the report, and supply the parameters to the report from the vb.net form, via maybe the url of the report?

i have been thinking on this for a while and i have not been able to come up with anything, im hoping someone here will have gone through a similar problem and will be able to help me out

if anyone could provide advice, tutorials, links on how to go about accomplishing this i would be eternally grateful!!!

regards

adam

after quite a bit of resarch i found it is in the MSDN library, you just have to search sql server reporting services.

this only allows passing of parameters... but if anyone knows how to pass a completely new query it would be greatly appreciated!

http://msdn2.microsoft.com/de-de/library/ms153586.aspx

hope this helps somebody!

regards

adam

Passing a Database name as a parameter

I'm currently testing a system that replicates data across a number of different databases. Once testing has been completed I use a stored procedure to reset the data on the master database so that additional tests can be run. I want to write a stored procedure that will reset the data on slave databases the testing has just been run on, but rather than have a stored procedure on each slave just have one on the master, as I could be testing across 1-n slaves.

I can access slave data from the master datbase with [slave1].[dbo].[target-table], and am looking at passing the slave name as a parameter to the stored procedure so the command would be [@.SlaveName].[dbo].[target-table], but any text inside the [ ] seems to be taken as literal string

Any one have any pointers?

If the count or names of your slave databases is/are likely to change then you'd have to do this using dynamic SQL. You could load a cursor with the contents of 'SELECT [name] FROM master.sys.databases WHERE <--insert criteria-->' then iterate through the cursor and create and execute a SQL string for each value that's returned.

For information only, there's an undocumented stored proc called sp_MSForEachDB that can be used to execute a SQL script in each database. However, be warned that undocumented stored procs could be dropped or significantly changed between SQL Server releases, or even service packs, so should not be used in production code.

Chris

|||

Solution I cam up was in the stored procedure to have a

EXEC ('USE '+@.SlaveName +'<action to take>')

Pass System date as parameter in OLAP report

Hi,

How to pass system date as parameter in a OLAP report? Also let me know how to pass parameter from One OLAP report to other?Hello,

I've used two separate methods for date driven reports:

1) Use the NOW() function to build strings representing members in your Date dimension. You would actually do this in the MDX.

2) Use data in your cube to dynamically determine what "today" is. In other words, find the last member in your Date dimension which has data and call that "today".

HTH

Todd

Partitions / Filegroups

Hi All,
Has anyone managed to link the system views (or any other mechanisms) to
display which partition number is in which filegroup?
e.g. As follows:
Table Name Partition_Number Filegroup_Name No_Rows
Demo_Table 1 OLD_DATA 4,000,000
Demo_Table 2 MED_DATA 3,000,000
Demo_Table 3 CURRENT_DATA 50,000
Thanks in advance,
DBX1
DBX1
Have you tried
SELECT * FROM sys.partitions ?
"DBX1" <DBX1@.discussions.microsoft.com> wrote in message
news:15F0C880-C509-4F68-8810-531DE6A89236@.microsoft.com...
> Hi All,
> Has anyone managed to link the system views (or any other mechanisms) to
> display which partition number is in which filegroup?
> e.g. As follows:
> Table Name Partition_Number Filegroup_Name No_Rows
> Demo_Table 1 OLD_DATA 4,000,000
> Demo_Table 2 MED_DATA 3,000,000
> Demo_Table 3 CURRENT_DATA 50,000
> Thanks in advance,
> DBX1
|||Yup, but this lists the partition something is in, and not the filegroup a
partition resides in, the closest I have come to making this work is the
following:
-- This is not very tidy code currently, and needs to be refactored
-- Partition_Table_Example : This is the table which we are interested in,
for filtering purposes in this example
select DS.destination_id , F.name,
PIX.obj_name, PIX.partition_number, PIX.[rows], PIX.[index_id]
from sys.destination_data_spaces DS
join sys.filegroups F
on DS.data_space_id = F.data_space_id
join (
SELECT OBJECT_NAME(P.[object_id]) as obj_name,
P.partition_number, P.[rows], P.[index_id]
FROM sys.partitions P
WHERE P.[object_id] = OBJECT_ID('[dbo].[Partition_Table_Example]')
) PIX
on PIX.Partition_number = DS.Partition_ID
-- This works, the interesting thing to note is that Partition_number links to
-- Destination_ID on the Partition_ID sys.partitions view
--There may be another link required between sys.partitions and
sys.destination_data_spaces if there are multiple partition schemes/functions
-- This has only been validated where there is a single partition_scheme
"Uri Dimant" wrote:

> DBX1
> Have you tried
> SELECT * FROM sys.partitions ?
>
>
>
> "DBX1" <DBX1@.discussions.microsoft.com> wrote in message
> news:15F0C880-C509-4F68-8810-531DE6A89236@.microsoft.com...
>
>
|||sys.partitions - will give the row-count for the table/individual parititons,
so that is not an issue, Im more interested in a join based query for all the
partitioned objects, I have manged to get something close, with the prior
querys for all objects.
It is missing something currently when there are multi-partition
defined/multi-partitioned objects as it returns some incorrect data.
The problem with the enclosed query is it is limited to a single object or
requires adding for obj_ids. and does not return the filegroup a particular
item or partition resides in.
What I am particulary interested in knowing is what sliced partition resides
in which filegroup.
I am aware that there can be multiple partitions in a single filegroup.
this does not list the filegroup names.
"Dejan Sarka" wrote:

> Hi!
>
> Partitions do not map one-to-one to filegroups. You can have multiple
> partitions on a single filegroup. You can find basic info about partitions
> in sys.partitions view or with $PARTITION function, like
> SELECT $PARTITION.myRangePF1(col1),
> COUNT(*)
> FROM PartitionTable
> GROUP BY $PARTITION.myRangePF1(col1)
> To find number of rows in different filegroups, the query gets complicated:
> WITH
> PartitionRowCount(Partition, NumberOfRowsInPartition) AS
> (
> SELECT $PARTITION.myRangePF1(col1),
> COUNT(*)
> FROM PartitionTable
> GROUP BY $PARTITION.myRangePF1(col1)
> ),
> PartitionsDataSpaces (partition_scheme_id, Partition, data_space_id,
> DataSpaceName) AS
> (
> SELECT dds.partition_scheme_id,
> dds.destination_id,
> dds.data_space_id,
> ds.name
> FROM sys.destination_data_spaces dds
> INNER JOIN sys.indexes i
> ON dds.partition_scheme_id = i.data_space_id
> INNER JOIN sys.data_spaces ds
> ON dds.data_space_id = ds.data_space_id
> WHERE i.object_id = OBJECT_ID('PartitionTable')
> AND i.index_id < 2
> )
> SELECT pds.DataSpaceName,
> SUM(prc.NumberOfRowsInPartition) AS NumberOfRowsInDataSpace
> FROM PartitionRowCount prc
> INNER JOIN PartitionsDataSpaces pds
> ON prc.Partition = pds.Partition
> GROUP BY pds.DataSpaceName;
> I hope I did not miss something.
> --
> Dejan Sarka
> http://www.solidqualitylearning.com/blogs/
>
>
|||Anyone able to validate the code below (I think this is producing the data
that I require):
select distinct object_name(SI.object_id),*
--F.name,SP.rows
from sys.destination_data_spaces DS
join sys.filegroups F on
F.data_space_id = DS.data_space_id
join sys.partition_schemes PS
on PS.data_space_id = DS.partition_scheme_id
join sys.indexes SI on
SI.data_space_id = DS.partition_scheme_id
join sys.partitions SP on
SP.object_id = SI.object_id and
SI.index_id = SP.index_id and
SP.partition_number = DS.destination_id
"DBX1" wrote:
[vbcol=seagreen]
> sys.partitions - will give the row-count for the table/individual parititons,
> so that is not an issue, Im more interested in a join based query for all the
> partitioned objects, I have manged to get something close, with the prior
> querys for all objects.
> It is missing something currently when there are multi-partition
> defined/multi-partitioned objects as it returns some incorrect data.
> The problem with the enclosed query is it is limited to a single object or
> requires adding for obj_ids. and does not return the filegroup a particular
> item or partition resides in.
> What I am particulary interested in knowing is what sliced partition resides
> in which filegroup.
> I am aware that there can be multiple partitions in a single filegroup.
> this does not list the filegroup names.
>
> "Dejan Sarka" wrote:

Partitions / Filegroups

Hi All,
Has anyone managed to link the system views (or any other mechanisms) to
display which partition number is in which filegroup?
e.g. As follows:
Table Name Partition_Number Filegroup_Name No_Rows
Demo_Table 1 OLD_DATA 4,000,000
Demo_Table 2 MED_DATA 3,000,000
Demo_Table 3 CURRENT_DATA 50,000
Thanks in advance,
DBX1DBX1
Have you tried
SELECT * FROM sys.partitions ?
"DBX1" <DBX1@.discussions.microsoft.com> wrote in message
news:15F0C880-C509-4F68-8810-531DE6A89236@.microsoft.com...
> Hi All,
> Has anyone managed to link the system views (or any other mechanisms) to
> display which partition number is in which filegroup?
> e.g. As follows:
> Table Name Partition_Number Filegroup_Name No_Rows
> Demo_Table 1 OLD_DATA 4,000,000
> Demo_Table 2 MED_DATA 3,000,000
> Demo_Table 3 CURRENT_DATA 50,000
> Thanks in advance,
> DBX1|||Hi!

> Has anyone managed to link the system views (or any other mechanisms) to
> display which partition number is in which filegroup?
> e.g. As follows:
> Table Name Partition_Number Filegroup_Name No_Rows
> Demo_Table 1 OLD_DATA 4,000,000
> Demo_Table 2 MED_DATA 3,000,000
> Demo_Table 3 CURRENT_DATA 50,000
Partitions do not map one-to-one to filegroups. You can have multiple
partitions on a single filegroup. You can find basic info about partitions
in sys.partitions view or with $PARTITION function, like
SELECT $PARTITION.myRangePF1(col1),
COUNT(*)
FROM PartitionTable
GROUP BY $PARTITION.myRangePF1(col1)
To find number of rows in different filegroups, the query gets complicated:
WITH
PartitionRowCount(Partition, NumberOfRowsInPartition) AS
(
SELECT $PARTITION.myRangePF1(col1),
COUNT(*)
FROM PartitionTable
GROUP BY $PARTITION.myRangePF1(col1)
),
PartitionsDataSpaces (partition_scheme_id, Partition, data_space_id,
DataSpaceName) AS
(
SELECT dds.partition_scheme_id,
dds.destination_id,
dds.data_space_id,
ds.name
FROM sys.destination_data_spaces dds
INNER JOIN sys.indexes i
ON dds.partition_scheme_id = i.data_space_id
INNER JOIN sys.data_spaces ds
ON dds.data_space_id = ds.data_space_id
WHERE i.object_id = OBJECT_ID('PartitionTable')
AND i.index_id < 2
)
SELECT pds.DataSpaceName,
SUM(prc.NumberOfRowsInPartition) AS NumberOfRowsInDataSpace
FROM PartitionRowCount prc
INNER JOIN PartitionsDataSpaces pds
ON prc.Partition = pds.Partition
GROUP BY pds.DataSpaceName;
I hope I did not miss something.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||Yup, but this lists the partition something is in, and not the filegroup a
partition resides in, the closest I have come to making this work is the
following:
-- This is not very tidy code currently, and needs to be refactored
-- Partition_Table_Example : This is the table which we are interested in,
for filtering purposes in this example
--
select DS.destination_id , F.name,
PIX.obj_name, PIX.partition_number, PIX.[rows], PIX.[index_id]
from sys.destination_data_spaces DS
join sys.filegroups F
on DS.data_space_id = F.data_space_id
join (
SELECT OBJECT_NAME(P.[object_id]) as obj_name,
P.partition_number, P.[rows], P.[index_id]
FROM sys.partitions P
WHERE P.[object_id] = OBJECT_ID('[dbo].[Partition_Table_Example]
')
) PIX
on PIX.Partition_number = DS.Partition_ID
-- This works, the interesting thing to note is that Partition_number links
to
-- Destination_ID on the Partition_ID sys.partitions view
--There may be another link required between sys.partitions and
sys.destination_data_spaces if there are multiple partition schemes/function
s
-- This has only been validated where there is a single partition_scheme
"Uri Dimant" wrote:

> DBX1
> Have you tried
> SELECT * FROM sys.partitions ?
>
>
>
> "DBX1" <DBX1@.discussions.microsoft.com> wrote in message
> news:15F0C880-C509-4F68-8810-531DE6A89236@.microsoft.com...
>
>|||sys.partitions - will give the row-count for the table/individual parititons
,
so that is not an issue, Im more interested in a join based query for all th
e
partitioned objects, I have manged to get something close, with the prior
querys for all objects.
It is missing something currently when there are multi-partition
defined/multi-partitioned objects as it returns some incorrect data.
The problem with the enclosed query is it is limited to a single object or
requires adding for obj_ids. and does not return the filegroup a particular
item or partition resides in.
What I am particulary interested in knowing is what sliced partition resides
in which filegroup.
I am aware that there can be multiple partitions in a single filegroup.
this does not list the filegroup names.
"Dejan Sarka" wrote:

> Hi!
>
> Partitions do not map one-to-one to filegroups. You can have multiple
> partitions on a single filegroup. You can find basic info about partitions
> in sys.partitions view or with $PARTITION function, like
> SELECT $PARTITION.myRangePF1(col1),
> COUNT(*)
> FROM PartitionTable
> GROUP BY $PARTITION.myRangePF1(col1)
> To find number of rows in different filegroups, the query gets complicated
:
> WITH
> PartitionRowCount(Partition, NumberOfRowsInPartition) AS
> (
> SELECT $PARTITION.myRangePF1(col1),
> COUNT(*)
> FROM PartitionTable
> GROUP BY $PARTITION.myRangePF1(col1)
> ),
> PartitionsDataSpaces (partition_scheme_id, Partition, data_space_id,
> DataSpaceName) AS
> (
> SELECT dds.partition_scheme_id,
> dds.destination_id,
> dds.data_space_id,
> ds.name
> FROM sys.destination_data_spaces dds
> INNER JOIN sys.indexes i
> ON dds.partition_scheme_id = i.data_space_id
> INNER JOIN sys.data_spaces ds
> ON dds.data_space_id = ds.data_space_id
> WHERE i.object_id = OBJECT_ID('PartitionTable')
> AND i.index_id < 2
> )
> SELECT pds.DataSpaceName,
> SUM(prc.NumberOfRowsInPartition) AS NumberOfRowsInDataSpace
> FROM PartitionRowCount prc
> INNER JOIN PartitionsDataSpaces pds
> ON prc.Partition = pds.Partition
> GROUP BY pds.DataSpaceName;
> I hope I did not miss something.
> --
> Dejan Sarka
> http://www.solidqualitylearning.com/blogs/
>
>|||> this does not list the filegroup names.
It lists data space names for filegroups. Data space names include filegroup
& partition scheme names.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||Anyone able to validate the code below (I think this is producing the data
that I require):
select distinct object_name(SI.object_id),*
-- F.name,SP.rows
from sys.destination_data_spaces DS
join sys.filegroups F on
F.data_space_id = DS.data_space_id
join sys.partition_schemes PS
on PS.data_space_id = DS.partition_scheme_id
join sys.indexes SI on
SI.data_space_id = DS.partition_scheme_id
join sys.partitions SP on
SP.object_id = SI.object_id and
SI.index_id = SP.index_id and
SP.partition_number = DS.destination_id
"DBX1" wrote:
[vbcol=seagreen]
> sys.partitions - will give the row-count for the table/individual paritito
ns,
> so that is not an issue, Im more interested in a join based query for all
the
> partitioned objects, I have manged to get something close, with the prior
> querys for all objects.
> It is missing something currently when there are multi-partition
> defined/multi-partitioned objects as it returns some incorrect data.
> The problem with the enclosed query is it is limited to a single object or
> requires adding for obj_ids. and does not return the filegroup a particula
r
> item or partition resides in.
> What I am particulary interested in knowing is what sliced partition resid
es
> in which filegroup.
> I am aware that there can be multiple partitions in a single filegroup.
> this does not list the filegroup names.
>
> "Dejan Sarka" wrote:
>

partitions

Hi,
I have a question in the middle beetween Windows and SQL.
I have RAID 5 system and then I can put SQL database and log file where i
want...
But..
Can be important create more logical partitions in RAID system for improve
defrag efficiency?
Example: I have only one database in SQL. I create a system partition C, a
database partition D and a log partition E.
Can I have better performance putting database in D and log in E?
Thank's a lot.. and sotty for my english..
Andrea
Since the drives are all a single Raid 5 you will not gain any performance by
spreading the data to the D drive and logs to the E drive.
"Rusty73" wrote:

> Hi,
> I have a question in the middle beetween Windows and SQL.
> I have RAID 5 system and then I can put SQL database and log file where i
> want...
> But..
> Can be important create more logical partitions in RAID system for improve
> defrag efficiency?
> Example: I have only one database in SQL. I create a system partition C, a
> database partition D and a log partition E.
> Can I have better performance putting database in D and log in E?
> Thank's a lot.. and sotty for my english..
> Andrea
>
>
|||I know that log files are writing sequentially to the disk btu with RAID 5 i
can decide to block the writing only on log disk...
but I can have no better performance to the defrag of log?
"fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...[vbcol=seagreen]
> Since the drives are all a single Raid 5 you will not gain any performance
> by
> spreading the data to the D drive and logs to the E drive.
>
> "Rusty73" wrote:
|||The best way to combat file fragmentation is to create the database files
once, as large as you will need them. If the files are allocated
contigueously, and never grow, then no file fragmentation will occur. Make
sure you give enough room for the Index Defrags, that will use internal data
pages, not file fragments to reorganize, again, as long as the file does not
grow.
Go ahead and leave the AUTOGROW feature on, but only as a fail-safe, in case
you've underestimated the space you will need.
Besides, file fragmentation will only affect scans and DSS type queries.
For a true OLTP system, the disk and database page access will be pretty
random, which will not be affected as much by file level fragmentation.
Sincerely,
Anthony Thomas

"Rusty73" <rusty77@.libero.it> wrote in message
news:%23iqlJn6tFHA.3932@.TK2MSFTNGP15.phx.gbl...
I know that log files are writing sequentially to the disk btu with RAID 5 i
can decide to block the writing only on log disk...
but I can have no better performance to the defrag of log?
"fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...[vbcol=seagreen]
> Since the drives are all a single Raid 5 you will not gain any performance
> by
> spreading the data to the D drive and logs to the E drive.
>
> "Rusty73" wrote:
|||Hi there,
Raid 5 will give you better performance for the DB.
For logs is best to create a mirror pair for improved performance, exactly
because of sequential writing.
My server is configured as follows:
system partition C - array of 2 mirrored drives - RAID 1
DB partition - array of a few disks in raid 5
log partition - another pair of mirrored drives - RAID 1
I agree with the previous comment that having different logical partitions
on the same disk array will not improve performance.
Hope this helps.
Kind regards,
Doru
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u7bc6z$tFHA.2848@.TK2MSFTNGP10.phx.gbl...
> The best way to combat file fragmentation is to create the database files
> once, as large as you will need them. If the files are allocated
> contigueously, and never grow, then no file fragmentation will occur.
> Make
> sure you give enough room for the Index Defrags, that will use internal
> data
> pages, not file fragments to reorganize, again, as long as the file does
> not
> grow.
> Go ahead and leave the AUTOGROW feature on, but only as a fail-safe, in
> case
> you've underestimated the space you will need.
> Besides, file fragmentation will only affect scans and DSS type queries.
> For a true OLTP system, the disk and database page access will be pretty
> random, which will not be affected as much by file level fragmentation.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Rusty73" <rusty77@.libero.it> wrote in message
> news:%23iqlJn6tFHA.3932@.TK2MSFTNGP15.phx.gbl...
> I know that log files are writing sequentially to the disk btu with RAID 5
> i
> can decide to block the writing only on log disk...
> but I can have no better performance to the defrag of log?
> "fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
> news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...
>

partitions

Hi,
I have a question in the middle beetween Windows and SQL.
I have RAID 5 system and then I can put SQL database and log file where i
want...
But..
Can be important create more logical partitions in RAID system for improve
defrag efficiency'
Example: I have only one database in SQL. I create a system partition C, a
database partition D and a log partition E.
Can I have better performance putting database in D and log in E?
Thank's a lot.. and sotty for my english..
AndreaSince the drives are all a single Raid 5 you will not gain any performance b
y
spreading the data to the D drive and logs to the E drive.
"Rusty73" wrote:

> Hi,
> I have a question in the middle beetween Windows and SQL.
> I have RAID 5 system and then I can put SQL database and log file where i
> want...
> But..
> Can be important create more logical partitions in RAID system for improve
> defrag efficiency'
> Example: I have only one database in SQL. I create a system partition C, a
> database partition D and a log partition E.
> Can I have better performance putting database in D and log in E?
> Thank's a lot.. and sotty for my english..
> Andrea
>
>|||I know that log files are writing sequentially to the disk btu with RAID 5 i
can decide to block the writing only on log disk...
but I can have no better performance to the defrag of log?
"fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...[vbcol=seagreen]
> Since the drives are all a single Raid 5 you will not gain any performance
> by
> spreading the data to the D drive and logs to the E drive.
>
> "Rusty73" wrote:
>|||The best way to combat file fragmentation is to create the database files
once, as large as you will need them. If the files are allocated
contigueously, and never grow, then no file fragmentation will occur. Make
sure you give enough room for the Index Defrags, that will use internal data
pages, not file fragments to reorganize, again, as long as the file does not
grow.
Go ahead and leave the AUTOGROW feature on, but only as a fail-safe, in case
you've underestimated the space you will need.
Besides, file fragmentation will only affect scans and DSS type queries.
For a true OLTP system, the disk and database page access will be pretty
random, which will not be affected as much by file level fragmentation.
Sincerely,
Anthony Thomas
"Rusty73" <rusty77@.libero.it> wrote in message
news:%23iqlJn6tFHA.3932@.TK2MSFTNGP15.phx.gbl...
I know that log files are writing sequentially to the disk btu with RAID 5 i
can decide to block the writing only on log disk...
but I can have no better performance to the defrag of log?
"fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...[vbcol=seagreen]
> Since the drives are all a single Raid 5 you will not gain any performance
> by
> spreading the data to the D drive and logs to the E drive.
>
> "Rusty73" wrote:
>|||Hi there,
Raid 5 will give you better performance for the DB.
For logs is best to create a mirror pair for improved performance, exactly
because of sequential writing.
My server is configured as follows:
system partition C - array of 2 mirrored drives - RAID 1
DB partition - array of a few disks in raid 5
log partition - another pair of mirrored drives - RAID 1
I agree with the previous comment that having different logical partitions
on the same disk array will not improve performance.
Hope this helps.
Kind regards,
Doru
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u7bc6z$tFHA.2848@.TK2MSFTNGP10.phx.gbl...
> The best way to combat file fragmentation is to create the database files
> once, as large as you will need them. If the files are allocated
> contigueously, and never grow, then no file fragmentation will occur.
> Make
> sure you give enough room for the Index Defrags, that will use internal
> data
> pages, not file fragments to reorganize, again, as long as the file does
> not
> grow.
> Go ahead and leave the AUTOGROW feature on, but only as a fail-safe, in
> case
> you've underestimated the space you will need.
> Besides, file fragmentation will only affect scans and DSS type queries.
> For a true OLTP system, the disk and database page access will be pretty
> random, which will not be affected as much by file level fragmentation.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Rusty73" <rusty77@.libero.it> wrote in message
> news:%23iqlJn6tFHA.3932@.TK2MSFTNGP15.phx.gbl...
> I know that log files are writing sequentially to the disk btu with RAID 5
> i
> can decide to block the writing only on log disk...
> but I can have no better performance to the defrag of log?
> "fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
> news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...
>

partitions

Hi,
I have a question in the middle beetween Windows and SQL.
I have RAID 5 system and then I can put SQL database and log file where i
want...
But..
Can be important create more logical partitions in RAID system for improve
defrag efficiency'
Example: I have only one database in SQL. I create a system partition C, a
database partition D and a log partition E.
Can I have better performance putting database in D and log in E?
Thank's a lot.. and sotty for my english..
AndreaSince the drives are all a single Raid 5 you will not gain any performance by
spreading the data to the D drive and logs to the E drive.
"Rusty73" wrote:
> Hi,
> I have a question in the middle beetween Windows and SQL.
> I have RAID 5 system and then I can put SQL database and log file where i
> want...
> But..
> Can be important create more logical partitions in RAID system for improve
> defrag efficiency'
> Example: I have only one database in SQL. I create a system partition C, a
> database partition D and a log partition E.
> Can I have better performance putting database in D and log in E?
> Thank's a lot.. and sotty for my english..
> Andrea
>
>|||I know that log files are writing sequentially to the disk btu with RAID 5 i
can decide to block the writing only on log disk...
but I can have no better performance to the defrag of log?
"fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...
> Since the drives are all a single Raid 5 you will not gain any performance
> by
> spreading the data to the D drive and logs to the E drive.
>
> "Rusty73" wrote:
>> Hi,
>> I have a question in the middle beetween Windows and SQL.
>> I have RAID 5 system and then I can put SQL database and log file where i
>> want...
>> But..
>> Can be important create more logical partitions in RAID system for
>> improve
>> defrag efficiency'
>> Example: I have only one database in SQL. I create a system partition C,
>> a
>> database partition D and a log partition E.
>> Can I have better performance putting database in D and log in E?
>> Thank's a lot.. and sotty for my english..
>> Andrea
>>|||The best way to combat file fragmentation is to create the database files
once, as large as you will need them. If the files are allocated
contigueously, and never grow, then no file fragmentation will occur. Make
sure you give enough room for the Index Defrags, that will use internal data
pages, not file fragments to reorganize, again, as long as the file does not
grow.
Go ahead and leave the AUTOGROW feature on, but only as a fail-safe, in case
you've underestimated the space you will need.
Besides, file fragmentation will only affect scans and DSS type queries.
For a true OLTP system, the disk and database page access will be pretty
random, which will not be affected as much by file level fragmentation.
Sincerely,
Anthony Thomas
"Rusty73" <rusty77@.libero.it> wrote in message
news:%23iqlJn6tFHA.3932@.TK2MSFTNGP15.phx.gbl...
I know that log files are writing sequentially to the disk btu with RAID 5 i
can decide to block the writing only on log disk...
but I can have no better performance to the defrag of log?
"fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...
> Since the drives are all a single Raid 5 you will not gain any performance
> by
> spreading the data to the D drive and logs to the E drive.
>
> "Rusty73" wrote:
>> Hi,
>> I have a question in the middle beetween Windows and SQL.
>> I have RAID 5 system and then I can put SQL database and log file where i
>> want...
>> But..
>> Can be important create more logical partitions in RAID system for
>> improve
>> defrag efficiency'
>> Example: I have only one database in SQL. I create a system partition C,
>> a
>> database partition D and a log partition E.
>> Can I have better performance putting database in D and log in E?
>> Thank's a lot.. and sotty for my english..
>> Andrea
>>|||Hi there,
Raid 5 will give you better performance for the DB.
For logs is best to create a mirror pair for improved performance, exactly
because of sequential writing.
My server is configured as follows:
system partition C - array of 2 mirrored drives - RAID 1
DB partition - array of a few disks in raid 5
log partition - another pair of mirrored drives - RAID 1
I agree with the previous comment that having different logical partitions
on the same disk array will not improve performance.
Hope this helps.
Kind regards,
Doru
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u7bc6z$tFHA.2848@.TK2MSFTNGP10.phx.gbl...
> The best way to combat file fragmentation is to create the database files
> once, as large as you will need them. If the files are allocated
> contigueously, and never grow, then no file fragmentation will occur.
> Make
> sure you give enough room for the Index Defrags, that will use internal
> data
> pages, not file fragments to reorganize, again, as long as the file does
> not
> grow.
> Go ahead and leave the AUTOGROW feature on, but only as a fail-safe, in
> case
> you've underestimated the space you will need.
> Besides, file fragmentation will only affect scans and DSS type queries.
> For a true OLTP system, the disk and database page access will be pretty
> random, which will not be affected as much by file level fragmentation.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Rusty73" <rusty77@.libero.it> wrote in message
> news:%23iqlJn6tFHA.3932@.TK2MSFTNGP15.phx.gbl...
> I know that log files are writing sequentially to the disk btu with RAID 5
> i
> can decide to block the writing only on log disk...
> but I can have no better performance to the defrag of log?
> "fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
> news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...
>> Since the drives are all a single Raid 5 you will not gain any
>> performance
>> by
>> spreading the data to the D drive and logs to the E drive.
>>
>> "Rusty73" wrote:
>> Hi,
>> I have a question in the middle beetween Windows and SQL.
>> I have RAID 5 system and then I can put SQL database and log file where
>> i
>> want...
>> But..
>> Can be important create more logical partitions in RAID system for
>> improve
>> defrag efficiency'
>> Example: I have only one database in SQL. I create a system partition C,
>> a
>> database partition D and a log partition E.
>> Can I have better performance putting database in D and log in E?
>> Thank's a lot.. and sotty for my english..
>> Andrea
>>
>