number of times procedure executes per second

hello all,

i have run into this problems a few times and don't really have a quick solution.

management wants to know how many times a procedure will execute in a second. i only have one procedure in mind and all it does is do a few different select and then returns a record set. so i am looking for a tool which will report how many times my "return customer profile" procedure can run. I have a tool which will execute a single procedure on multiple threads, but it has no reporting. If I have something to execute the procedure how can I report back on it's performance?

In perfmon there is a "transaction per second" however this does not count selects. so it is always 0.

Is there any native sql 2000 2005 tool to do this?

Is there any inexpensive 3 party tool which people use?

thanks in advance.

In SQL 2005 if you run the SP then one of the standard reports in management studio should tell you how many times it has run. You know how long the test was and thus how many times the SP can run /s.

You can also look at the SQL Batches/sec in perfmon, this should give you the figure you are after as long as that is the only thing you will be doing on the server.

Number of IOs Per Second?

I need to size a storage system. I know that I will have two tables, each
with eight million records. I will have 10 users, each of whom will be
doing complex reports simultaneously, each of which does joins against those
eight million record tables.
What are some guidelines for best and and worst case, about the number of
IOs per second that this system will generate? I need to make sure my
RAID arrays have enough physical disks to satisfy this number of IOs.
If the database is 2 TB in size, what is the desired amount of cache memory?
Will
westes AT earthbroadcast.com
I also found a more recent article
http://www.microsoft.com/technet/pro.../rdbmspft.mspx
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CHANGE USERNAME TO westes" <DELETE_westes@.earthbroadcast.com> wrote in
message news:u0fS9ExfEHA.1656@.TK2MSFTNGP10.phx.gbl...
> I need to size a storage system. I know that I will have two tables,
each
> with eight million records. I will have 10 users, each of whom will be
> doing complex reports simultaneously, each of which does joins against
those
> eight million record tables.
> What are some guidelines for best and and worst case, about the number of
> IOs per second that this system will generate? I need to make sure
my
> RAID arrays have enough physical disks to satisfy this number of IOs.
> If the database is 2 TB in size, what is the desired amount of cache
memory?
> --
> Will
> westes AT earthbroadcast.com
>
|||There is a great article by Henry Lau about this. it is targeted at SQL 7,
but the concepts still apply
http://msdn.microsoft.com/library/de...l7perftune.asp
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CHANGE USERNAME TO westes" <DELETE_westes@.earthbroadcast.com> wrote in
message news:u0fS9ExfEHA.1656@.TK2MSFTNGP10.phx.gbl...
> I need to size a storage system. I know that I will have two tables,
each
> with eight million records. I will have 10 users, each of whom will be
> doing complex reports simultaneously, each of which does joins against
those
> eight million record tables.
> What are some guidelines for best and and worst case, about the number of
> IOs per second that this system will generate? I need to make sure
my
> RAID arrays have enough physical disks to satisfy this number of IOs.
> If the database is 2 TB in size, what is the desired amount of cache
memory?
> --
> Will
> westes AT earthbroadcast.com
>
sql

Number of IOs Per Second?

I need to size a storage system. I know that I will have two tables, each
with eight million records. I will have 10 users, each of whom will be
doing complex reports simultaneously, each of which does joins against those
eight million record tables.
What are some guidelines for best and and worst case, about the number of
IOs per second that this system will generate? I need to make sure my
RAID arrays have enough physical disks to satisfy this number of IOs.
If the database is 2 TB in size, what is the desired amount of cache memory?
Will
westes AT earthbroadcast.comI also found a more recent article
http://www.microsoft.com/technet/pr...n/rdbmspft.mspx
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CHANGE USERNAME TO westes" <DELETE_westes@.earthbroadcast.com> wrote in
message news:u0fS9ExfEHA.1656@.TK2MSFTNGP10.phx.gbl...
> I need to size a storage system. I know that I will have two tables,
each
> with eight million records. I will have 10 users, each of whom will be
> doing complex reports simultaneously, each of which does joins against
those
> eight million record tables.
> What are some guidelines for best and and worst case, about the number of
> IOs per second that this system will generate? I need to make sure
my
> RAID arrays have enough physical disks to satisfy this number of IOs.
> If the database is 2 TB in size, what is the desired amount of cache
memory?
> --
> Will
> westes AT earthbroadcast.com
>|||There is a great article by Henry Lau about this. it is targeted at SQL 7,
but the concepts still apply
sql7perftune.asp" target="_blank">http://msdn.microsoft.com/library/d...ql7perftune.asp
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CHANGE USERNAME TO westes" <DELETE_westes@.earthbroadcast.com> wrote in
message news:u0fS9ExfEHA.1656@.TK2MSFTNGP10.phx.gbl...
> I need to size a storage system. I know that I will have two tables,
each
> with eight million records. I will have 10 users, each of whom will be
> doing complex reports simultaneously, each of which does joins against
those
> eight million record tables.
> What are some guidelines for best and and worst case, about the number of
> IOs per second that this system will generate? I need to make sure
my
> RAID arrays have enough physical disks to satisfy this number of IOs.
> If the database is 2 TB in size, what is the desired amount of cache
memory?
> --
> Will
> westes AT earthbroadcast.com
>

Number of IOs Per Second?

I need to size a storage system. I know that I will have two tables, each
with eight million records. I will have 10 users, each of whom will be
doing complex reports simultaneously, each of which does joins against those
eight million record tables.
What are some guidelines for best and and worst case, about the number of
IOs per second that this system will generate? I need to make sure my
RAID arrays have enough physical disks to satisfy this number of IOs.
If the database is 2 TB in size, what is the desired amount of cache memory?
--
Will
westes AT earthbroadcast.comI also found a more recent article
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CHANGE USERNAME TO westes" <DELETE_westes@.earthbroadcast.com> wrote in
message news:u0fS9ExfEHA.1656@.TK2MSFTNGP10.phx.gbl...
> I need to size a storage system. I know that I will have two tables,
each
> with eight million records. I will have 10 users, each of whom will be
> doing complex reports simultaneously, each of which does joins against
those
> eight million record tables.
> What are some guidelines for best and and worst case, about the number of
> IOs per second that this system will generate? I need to make sure
my
> RAID arrays have enough physical disks to satisfy this number of IOs.
> If the database is 2 TB in size, what is the desired amount of cache
memory?
> --
> Will
> westes AT earthbroadcast.com
>|||There is a great article by Henry Lau about this. it is targeted at SQL 7,
but the concepts still apply
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/msdn_sql7perftune.asp
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CHANGE USERNAME TO westes" <DELETE_westes@.earthbroadcast.com> wrote in
message news:u0fS9ExfEHA.1656@.TK2MSFTNGP10.phx.gbl...
> I need to size a storage system. I know that I will have two tables,
each
> with eight million records. I will have 10 users, each of whom will be
> doing complex reports simultaneously, each of which does joins against
those
> eight million record tables.
> What are some guidelines for best and and worst case, about the number of
> IOs per second that this system will generate? I need to make sure
my
> RAID arrays have enough physical disks to satisfy this number of IOs.
> If the database is 2 TB in size, what is the desired amount of cache
memory?
> --
> Will
> westes AT earthbroadcast.com
>

Number Format

When setting the format property for a textbox containing a number the first parameter relates to a positive number, the second negative and the third to zero.

eg #,###; (#,###); 0

Is there a way of also formatting Null values in the same way without writing custom code?

Could you do an isnull in your query so that you don't get NULL showing on your report?

Jarret

|||

I could. Just means I have to either type this in for every column in my dataset or every textbox (instead of doing a multi-select). Sounds a bit lazy I know but I have a lot of reports and columns to go through.

Mark

|||

Sorry Mark, I can't seem to find anything that will replace NULL with a 0 just by changing using the format code.

I think you're best bet is using the ISNULL or an expression, but either way, you'd have to change each field.

Jarret

Null Left Join

Hi guys,
I need to get data from a join between two tables, but I just want the data that exists in first table and not in second one. It's like:

SELECT t1.*
FROM t1
LEFT JOIN t2 ON t1.col1 = t2.col1
WHERE t2.col1 IS NULL

I'm using the merge join transformation, but I can't find no options to put the WHERE code in the join. How can I do that? Anyone can help me?

Cheers!Just use the Left Outer Join option under Join Type. That will output all rows from the left input, with any matching rows from the right input. The where clause is not necessary.|||

jwelch wrote:

Just use the Left Outer Join option under Join Type. That will output all rows from the left input, with any matching rows from the right input. The where clause is not necessary.

And then add a conditional split to kick out the rows that are not null from the second source (the right side of the merge join) so as to get a resultset of rows from the left that don't exist in the right.

John's approach is just part of the answer if you want to show only records that don't exist in the right side.|||Phil is correct. Too much caffeine today, or not enough, I don't know which |||

Phil Brammer wrote:

And then add a conditional split to kick out the rows that are not null from the second source (the right side of the merge join) so as to get a resultset of rows from the left that don't exist in the right.

John's approach is just part of the answer if you want to show only records that don't exist in the right side.

I can't believe I can't see it. Man, you're good. Thanks a lot!