Performance tuning and measure on MSSQL 2000

Hi

I am trying to design an IO subsystem for my SQL Server and for that I
need to try and predict IO activity on each table in my MSSQL
Database. My idea is to move the hottest tables into special disk
subsystem away from the less hotter tables. So far I have gathered
that we have three tables more hot than the others but I have no
feeling on ratio on how hot each is and how much activity is on the
less hotter tables. I need to predict how many disks I should assign
to each subsystem and so far...
I haven't found a reasonable way to do this.

The only way I found to see read/writes and physical read/writes is on
filelevel. but I've also managed to do a trace in sqlprofiler to get
the logical read and writes per query but since my queries are often
joins I have no way of spliting that IO between the tables included in
the join and no idea on which hit the buffer pool and which didn'nt.
Is there maybe a counter or some way that I have not found?

Any input would be greatly appriciated.

best regards & thanks
Arni Snorriarnie@.gormur.com (Arni Snorri Eggertsson) wrote in message news:<c8d15bfa.0404280125.6f1dadcf@.posting.google.com>...
> Hi
> I am trying to design an IO subsystem for my SQL Server and for that I
> need to try and predict IO activity on each table in my MSSQL
> Database. My idea is to move the hottest tables into special disk
> subsystem away from the less hotter tables. So far I have gathered
> that we have three tables more hot than the others but I have no
> feeling on ratio on how hot each is and how much activity is on the
> less hotter tables. I need to predict how many disks I should assign
> to each subsystem and so far...
> I haven't found a reasonable way to do this.
> The only way I found to see read/writes and physical read/writes is on
> filelevel. but I've also managed to do a trace in sqlprofiler to get
> the logical read and writes per query but since my queries are often
> joins I have no way of spliting that IO between the tables included in
> the join and no idea on which hit the buffer pool and which didn'nt.
> Is there maybe a counter or some way that I have not found?
> Any input would be greatly appriciated.
> best regards & thanks
> Arni Snorri

I'm not sure if it's possible to do exactly what you want - MSSQL will
probably cache a lot of the data from the 'hot' tables anyway, so the
issue is not so much the physical disk access as how much RAM you
have, and how well MSSQL uses the cache. There are a lot of
performance monitor counters for buffer and cache management you can
use to look at this.

As for the disks, I would start by identifying how much space is
required on disk, then try to use lots of smaller disks instead of
fewer bigger ones for the 'hot' filegroups. Placing the transaction
logs on separate disks would also help, of course.

Simon|||"Arni Snorri Eggertsson" <arnie@.gormur.com> wrote in message
news:c8d15bfa.0404280125.6f1dadcf@.posting.google.c om...
> Hi
> I am trying to design an IO subsystem for my SQL Server and for that I
> need to try and predict IO activity on each table in my MSSQL
> Database. My idea is to move the hottest tables into special disk
> subsystem away from the less hotter tables. So far I have gathered
> that we have three tables more hot than the others but I have no
> feeling on ratio on how hot each is and how much activity is on the
> less hotter tables. I need to predict how many disks I should assign
> to each subsystem and so far...
> I haven't found a reasonable way to do this.

If you don't have it, get the Microsoft Press book on SQL Server Performance
tuning. Lots of good help here.

> The only way I found to see read/writes and physical read/writes is on
> filelevel. but I've also managed to do a trace in sqlprofiler to get
> the logical read and writes per query but since my queries are often
> joins I have no way of spliting that IO between the tables included in
> the join and no idea on which hit the buffer pool and which didn'nt.
> Is there maybe a counter or some way that I have not found?
> Any input would be greatly appriciated.
> best regards & thanks
> Arni Snorri

Performance Problem with UNION Query

Hi ,
I must say firstly, my design is a little stupid but It has to like that,
So , I have a table named XX with 98 columns and 230.000 records , it is old
data source and I can't cut into pieces it.
and I have already new datas with new design , I did new view named YY
similiar with XX, and I wanna to merge two structures,
my union query is like that
SELECT column1, column2, .... column98 FROM YY WHERE column5='aaaaaaaa'
UNION
SELECT column1, column2, .... column98 FROM XX WHERE column5='aaaaaaaa'
this query works slowly for me , how can I make more effiency that
structure?
sorry If I couldn't explain very well.
Thanks for helps
Best Regards
Serkan KARAAssuming the design is not open for discussion:
First step is to determine whether you need to remove duplicated after the U
NION is performed. If
no, change to UNION ALL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"scorpion" <ss@.ss.com> wrote in message news:%23OI3I0UBFHA.2580@.TK2MSFTNGP10.phx.gbl...[col
or=darkred]
> Hi ,
> I must say firstly, my design is a little stupid but It has to like that,
> So , I have a table named XX with 98 columns and 230.000 records , it is o
ld
> data source and I can't cut into pieces it.
> and I have already new datas with new design , I did new view named YY
> similiar with XX, and I wanna to merge two structures,
> my union query is like that
> SELECT column1, column2, .... column98 FROM YY WHERE column5='aaaaaaaa'
> UNION
> SELECT column1, column2, .... column98 FROM XX WHERE column5='aaaaaaaa'
> this query works slowly for me , how can I make more effiency that
> structure?
> sorry If I couldn't explain very well.
> Thanks for helps
> Best Regards
> Serkan KARA
>[/color]|||Change UNION to UNION ALL.
Run the query and check the plan. Look to see if you have indexes. How
many rows to you expect to return, lots, or very few? Can you index the
view (check in books online, or just try.) Is this going to be executed a
lot? And by slow, do you mean oppressively slow, or just kind of slow.
First step though is to check the plan and look for major trouble spots.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"scorpion" <ss@.ss.com> wrote in message
news:%23OI3I0UBFHA.2580@.TK2MSFTNGP10.phx.gbl...
> Hi ,
> I must say firstly, my design is a little stupid but It has to like that,
> So , I have a table named XX with 98 columns and 230.000 records , it is
> old
> data source and I can't cut into pieces it.
> and I have already new datas with new design , I did new view named YY
> similiar with XX, and I wanna to merge two structures,
> my union query is like that
> SELECT column1, column2, .... column98 FROM YY WHERE column5='aaaaaaaa'
> UNION
> SELECT column1, column2, .... column98 FROM XX WHERE column5='aaaaaaaa'
> this query works slowly for me , how can I make more effiency that
> structure?
> sorry If I couldn't explain very well.
> Thanks for helps
> Best Regards
> Serkan KARA
>

Performance optimization in SSIS

Hi,

our package have design like this,

OLEDBSource à Derived Column à Lookup

|

Matching Records Un Matched Records

| |

OLEDBCommandOLEDBDestination

(Update)(Insert)

and our source & destination table are oracle. when we execute the package the performance is very low and some times its showing like processing ( yellow color) even for 1 hrs .what could be the problem.can any one help us.is there any reason like when we use orcale database this will slow down the performance of package

Jegan

There are plenty of areas which could cause performance problems, just work through them logically. From a pure SSIS perspective, lookups can be slow either building the cache, or when not using caching. The OLEDB Command can also be slow because it is row by row processing. The OLEDB Destination is not great for Oracle, because they don't have fast load support in OLE-DB. There is a third party driver which is I believe faster.

Saying all that, start with the basics. What networks are there between the Source Machine -> SSIS Machine -> Destination Machine, as the data will follow that path.

Isolate the components and test them individually to identify any bottle necks.

e.g.

Source -> Trash Destination - Is the source query, extract or network hop slow

Stage the source data in a raw file in the SSIS machine, then use a Raw File Source -> Lookup -> 2 X Trash Destinations, see if the lookup performance is slow.

etc

Trash Destination is just a freebie transform on http://www.sqlis.com, which we wrote to help build test scenarios faster, and it is then obvious this is not a "real" package, but you can use other transforms just don't add an output. Row Number or Union work quite well as they don't do much on their own.

|||

In addition to what Darren said you should try and apply the OVAL concept to investigatig performance problems. OVAL was introduced by Donald Farmer in a Technet webcast and I talk about it here:

Donald Farmer's Technet webcast
(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx)

I highly recommend you watch the webcast.

Also, the Oracle driver that Darren spoke of is provided by Persistent: http://www.persistentsys.com/products/ssisoracleconn/ssisoracleconn_features.htm

Also, Scott Barrett has lots of experience of using SSIS with oracle and irs worth checking out his blog: http://microsoftdw.blogspot.com/

-Jamie

|||

Jegant wrote:

Hi,

our package have design like this,

OLEDBSource à Derived Column à Lookup

|

Matching Records Un Matched Records

| |

OLEDBCommand OLEDBDestination

(Update) (Insert)

and our source & destination table are oracle. when we execute the package the performance is very low and some times its showing like processing ( yellow color) even for 1 hrs .what could be the problem.can any one help us.is there any reason like when we use orcale database this will slow down the performance of package

Jegan

Looking only at the SSIS side, here is my bet: the OLE DB Command. How many rows are you processing? and how many of those are going to the update pipeline?

As a general, and very personal, practice I always replace the OLEDB Command by an OLE DB destination pointing to a temporary table that is empty at the beginning of every execution. Then back in the control flow I use an 'Execute SQL Task' to perform a one time update using the content of the temp table. I had similar performance issues using the approach you are describing in your post and this simple change made a huge difference. Other suggestion is to try to isolate the issue; for example replace the OLE DB command by a RowCount transformation and measure your execution time; then try the same with the OLE DB Destination. This paper has very good tips on Performance Tuning Techniques:

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx

Rafael Salas

Performance of Encryption algorithms

I am in the stage of design for an application that uses SQL server 2005. We intended to encrypt some sensitve data using the encryption features in SQL server 2005. we will use symmetric key encryption. The question here is which symmetric encryption algorithm has the best performance? how much does the key size affect the perfromance? the data to be encrypted will be some lines of text equal to a word document.
any ideas?

Hi Muhammad,

We don't have explicit numbers for the performance of different algorithms/key lengths yet but from what we have seen there is no significant difference. The biggest performance impact is how the data is stored (i.e. storing encrypted data across very many rows in lots of columns and then selecting every column and every row is much more expensive than selectively retrieving a small set of rows/columns).

If you would like more details, please let me know,

Sung