Number of users

Quick question, is there a limitation on the number of users that can get into SQL server at one time? Say 1500 or 2000 users? If not, I wonder how many users can actually cause the performance of the server to have a significant drop.
Thanks
SHKThere is a pretty hard limit at 32000 users, but I think it could be broken given sufficient hardware. I'm not sure that it makes any sense to even go there, other than as an academic exercise because it smacks of poor system design. A federated replicated approach allows practically infinite growth from an application perspective.

Most servers will crunch LONG before you even begin to approach the limits of the database engine. The number of supportable users usually depends on the RAM use more than anything, although the I/O bus often factors into that decision too.

-PatP|||Are you talking about connections or users?

You should keep your transactions short AND your connections as few as possible...

Get in Get out...

I use connection pooling, and even though I may have thousands of users, I'll only up a set number of connections..that stay open...for connection pooling purposes...

Why do you ask?

Sounds like a web app

Number of statements during a specific time

Dear fellows,
I'd like to see how many queries are launched in a hour in a live server. In
order to achieve such information I've though in to open profiler applicatio
n
and then save that information on a .TRC file (encompassing two hours for
example). After that, by DTS export that file to a table and to retrieve the
number of SELECT commited with the help of specific strings (Textdata and
loginname). That will mean know the number of queries launched for our
developers from its QA sessions as well as queries and another things for an
y
kind of processes (If I am not wrong)
It take part of a set of measures intended to demonstrate that we'd need
news hardware requirements, hd, and so on.
Any help/though/advice of how do this better? In aid of faster or reliabilit
y.
Thanks a lot for your input,It is not a good solution due to I've got now 140mb of tracefile for just a
hour!!
Let me know any idea...
"Enric" wrote:

> Dear fellows,
> I'd like to see how many queries are launched in a hour in a live server.
In
> order to achieve such information I've though in to open profiler applicat
ion
> and then save that information on a .TRC file (encompassing two hours for
> example). After that, by DTS export that file to a table and to retrieve t
he
> number of SELECT commited with the help of specific strings (Textdata and
> loginname). That will mean know the number of queries launched for our
> developers from its QA sessions as well as queries and another things for
any
> kind of processes (If I am not wrong)
> It take part of a set of measures intended to demonstrate that we'd need
> news hardware requirements, hd, and so on.
> Any help/though/advice of how do this better? In aid of faster or reliabil
ity.
> Thanks a lot for your input,|||try use performans counters - perfmon (sqlserver:sqlserverstatistics)
but... is is not idealy metod to demonstrate that you need new hardware...
--
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Enric" wrote:

> Dear fellows,
> I'd like to see how many queries are launched in a hour in a live server.
In
> order to achieve such information I've though in to open profiler applicat
ion
> and then save that information on a .TRC file (encompassing two hours for
> example). After that, by DTS export that file to a table and to retrieve t
he
> number of SELECT commited with the help of specific strings (Textdata and
> loginname). That will mean know the number of queries launched for our
> developers from its QA sessions as well as queries and another things for
any
> kind of processes (If I am not wrong)
> It take part of a set of measures intended to demonstrate that we'd need
> news hardware requirements, hd, and so on.
> Any help/though/advice of how do this better? In aid of faster or reliabil
ity.
> Thanks a lot for your input,

Number of seconds Between 2 Dates

Hi,
Can anyone tell me a way of calculating the number of seconds that have
elapsed between 2 date time fields e.g. I would to know the total number of
seconds
between GetDate() and GetDate() - 1
Thanks,
David.Use datediff function.
SELECT Datediff(ss,firstdate, seconddate) as elapsedSec
"David Lee" <dleeid@.internode.on.net> wrote in message
news:123p5tnn2r9b52d@.corp.supernews.com...
> Hi,
> Can anyone tell me a way of calculating the number of seconds that have
> elapsed between 2 date time fields e.g. I would to know the total number
of
> seconds
> between GetDate() and GetDate() - 1
> Thanks,
> David.
>|||Make use of DateDiff function.
Select dateDiff(ss, <<startDate>>, <<EndDate>> )
Best Regards
Vadivel
http://vadivel.blogspot.com
"David Lee" wrote:

> Hi,
> Can anyone tell me a way of calculating the number of seconds that have
> elapsed between 2 date time fields e.g. I would to know the total number o
f
> seconds
> between GetDate() and GetDate() - 1
> Thanks,
> David.
>
>

Number of Bytes Read/Write Per Day

Does anybody know how to get the total number of bytes read/write for a
period
of time on an instance of SQL Server (No include other instances on the same
server)?
Thanks,
Lijun
Hi
@.@.TOTAL_READ and @.@.TOTAL_WRITE give the number of disk reads (not cache
reads) and writes (respectively) by Microsoft SQL Server since the instance
last started. If you run this at the start of the period and then at the end
you can subtract the two values. sp_monitor will return these value and
others.
John
"Lijun Zhang" wrote:

> Does anybody know how to get the total number of bytes read/write for a
> period
> of time on an instance of SQL Server (No include other instances on the same
> server)?
> Thanks,
> Lijun
>
>
|||Lijun;
If I understand what you are trying to accomplish from an earlier thread in
this newsgroup, I'd say that these total numbers of bytes per day would not
be very useful. To determine the I/O bandwidth requirements or to determine
how your current I/O bandwith is being used in order to determine what I/O
bandwidth you may need to acquire, I'd track the I/O perfmon counters over
time and identify the specific I/O bottlenecks, if any. and their specific
nature.
Knowing only the total number of bytes read or written doesn't tell you much
about how the I/O subsystem is used. For instance, if you are doing a lot of
large sequential I/Os, you can pump through far more bytes on a given I/O
path than if you are doing small random I/Os.
Linchi
"Lijun Zhang" wrote:

> Does anybody know how to get the total number of bytes read/write for a
> period
> of time on an instance of SQL Server (No include other instances on the same
> server)?
> Thanks,
> Lijun
>
>

Number of Bytes Read/Write Per Day

Does anybody know how to get the total number of bytes read/write for a
period
of time on an instance of SQL Server (No include other instances on the same
server)?
Thanks,
LijunHi
@.@.TOTAL_READ and @.@.TOTAL_WRITE give the number of disk reads (not cache
reads) and writes (respectively) by Microsoft SQL Server since the instance
last started. If you run this at the start of the period and then at the end
you can subtract the two values. sp_monitor will return these value and
others.
John
"Lijun Zhang" wrote:
> Does anybody know how to get the total number of bytes read/write for a
> period
> of time on an instance of SQL Server (No include other instances on the same
> server)?
> Thanks,
> Lijun
>
>|||Lijun;
If I understand what you are trying to accomplish from an earlier thread in
this newsgroup, I'd say that these total numbers of bytes per day would not
be very useful. To determine the I/O bandwidth requirements or to determine
how your current I/O bandwith is being used in order to determine what I/O
bandwidth you may need to acquire, I'd track the I/O perfmon counters over
time and identify the specific I/O bottlenecks, if any. and their specific
nature.
Knowing only the total number of bytes read or written doesn't tell you much
about how the I/O subsystem is used. For instance, if you are doing a lot of
large sequential I/Os, you can pump through far more bytes on a given I/O
path than if you are doing small random I/Os.
Linchi
"Lijun Zhang" wrote:
> Does anybody know how to get the total number of bytes read/write for a
> period
> of time on an instance of SQL Server (No include other instances on the same
> server)?
> Thanks,
> Lijun
>
>

Number of Bytes Read/Write Per Day

Does anybody know how to get the total number of bytes read/write for a
period
of time on an instance of SQL Server (No include other instances on the same
server)?
Thanks,
LijunHi
@.@.TOTAL_READ and @.@.TOTAL_WRITE give the number of disk reads (not cache
reads) and writes (respectively) by Microsoft SQL Server since the instance
last started. If you run this at the start of the period and then at the end
you can subtract the two values. sp_monitor will return these value and
others.
John
"Lijun Zhang" wrote:

> Does anybody know how to get the total number of bytes read/write for a
> period
> of time on an instance of SQL Server (No include other instances on the sa
me
> server)?
> Thanks,
> Lijun
>
>|||Lijun;
If I understand what you are trying to accomplish from an earlier thread in
this newsgroup, I'd say that these total numbers of bytes per day would not
be very useful. To determine the I/O bandwidth requirements or to determine
how your current I/O bandwith is being used in order to determine what I/O
bandwidth you may need to acquire, I'd track the I/O perfmon counters over
time and identify the specific I/O bottlenecks, if any. and their specific
nature.
Knowing only the total number of bytes read or written doesn't tell you much
about how the I/O subsystem is used. For instance, if you are doing a lot of
large sequential I/Os, you can pump through far more bytes on a given I/O
path than if you are doing small random I/Os.
Linchi
"Lijun Zhang" wrote:

> Does anybody know how to get the total number of bytes read/write for a
> period
> of time on an instance of SQL Server (No include other instances on the sa
me
> server)?
> Thanks,
> Lijun
>
>

Number and duration of execution for stored procedures

Hello,
Will be very usefull for me if I know what are the stored procedures more
often use in a database and how long is the execution time.Can someone help
me in putting a trigger, or getting this values from tempdb maybe ? I know
profiler (RPC completed I guess) , but I can't understand duration -
sometimes, if that are ms, I think it's not best values. A trigger well put i
think will help me better, cause I can make after that an alaysis for a week
time and take propper decision for code optimization. I put in some
procedures a code with getdate at start, enddate at the end, procedure id,
etc, but there are a lot of procedures.
Thank you
CM
I'd run SQL Server Profiler to group by Duration event to see how long it
is running
"CM" <CM@.discussions.microsoft.com> wrote in message
news:9C916A15-895F-4F0C-B1DD-D44F41B29BAC@.microsoft.com...
> Hello,
> Will be very usefull for me if I know what are the stored procedures more
> often use in a database and how long is the execution time.Can someone
> help
> me in putting a trigger, or getting this values from tempdb maybe ? I know
> profiler (RPC completed I guess) , but I can't understand duration -
> sometimes, if that are ms, I think it's not best values. A trigger well
> put i
> think will help me better, cause I can make after that an alaysis for a
> week
> time and take propper decision for code optimization. I put in some
> procedures a code with getdate at start, enddate at the end, procedure id,
> etc, but there are a lot of procedures.
> Thank you

Number and duration of execution for stored procedures

Hello,
Will be very usefull for me if I know what are the stored procedures more
often use in a database and how long is the execution time.Can someone help
me in putting a trigger, or getting this values from tempdb maybe ? I know
profiler (RPC completed I guess) , but I can't understand duration -
sometimes, if that are ms, I think it's not best values. A trigger well put i
think will help me better, cause I can make after that an alaysis for a week
time and take propper decision for code optimization. I put in some
procedures a code with getdate at start, enddate at the end, procedure id,
etc, but there are a lot of procedures.
Thank youCM
I'd run SQL Server Profiler to group by Duration event to see how long it
is running
"CM" <CM@.discussions.microsoft.com> wrote in message
news:9C916A15-895F-4F0C-B1DD-D44F41B29BAC@.microsoft.com...
> Hello,
> Will be very usefull for me if I know what are the stored procedures more
> often use in a database and how long is the execution time.Can someone
> help
> me in putting a trigger, or getting this values from tempdb maybe ? I know
> profiler (RPC completed I guess) , but I can't understand duration -
> sometimes, if that are ms, I think it's not best values. A trigger well
> put i
> think will help me better, cause I can make after that an alaysis for a
> week
> time and take propper decision for code optimization. I put in some
> procedures a code with getdate at start, enddate at the end, procedure id,
> etc, but there are a lot of procedures.
> Thank you

Number and duration of execution for stored procedures

Hello,
Will be very usefull for me if I know what are the stored procedures more
often use in a database and how long is the execution time.Can someone help
me in putting a trigger, or getting this values from tempdb maybe ? I know
profiler (RPC completed I guess) , but I can't understand duration -
sometimes, if that are ms, I think it's not best values. A trigger well put
i
think will help me better, cause I can make after that an alaysis for a week
time and take propper decision for code optimization. I put in some
procedures a code with getdate at start, enddate at the end, procedure id,
etc, but there are a lot of procedures.
Thank youCM
I'd run SQL Server Profiler to group by Duration event to see how long it
is running
"CM" <CM@.discussions.microsoft.com> wrote in message
news:9C916A15-895F-4F0C-B1DD-D44F41B29BAC@.microsoft.com...
> Hello,
> Will be very usefull for me if I know what are the stored procedures more
> often use in a database and how long is the execution time.Can someone
> help
> me in putting a trigger, or getting this values from tempdb maybe ? I know
> profiler (RPC completed I guess) , but I can't understand duration -
> sometimes, if that are ms, I think it's not best values. A trigger well
> put i
> think will help me better, cause I can make after that an alaysis for a
> week
> time and take propper decision for code optimization. I put in some
> procedures a code with getdate at start, enddate at the end, procedure id,
> etc, but there are a lot of procedures.
> Thank you

NullProcessing on Dimension keys

Running in to the following problem

Have a dimension (time dimension) with a datetime keyfield. Have a fact table with a date field that is sometimes not filled in. A regular relationship exists in the dimension usage tab between this time dimension and the measure group of the fact table based on the date field.

So I set up the time dimension with dimesion property UnknownMember visible, set the NullProcessing property of the keyColumn of the dimension keyfield (you're still folowing ;-) to UnknownMember because I want to avoid the default beheaviour of SSAS making it 0 or blank.

Deploy the thing and get the following 2 warnings:

Warning 1 Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_ProjectCard, Column: ProjectDateSold, Value: 0:00:00. 0 0
Warning 2 Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Time of Dimension: Project Sold Date from Database: CDWH OLAP database, Cube: CDWH, Measure Group: Project Card, Partition: Project Card, Record: 8. 0 0

Both of them seem perfectly normal (Notice that the value of warning 1 represents the NULL date) and that warning 2 actualy tells me that it was converted to an unknown member, but then I get 3 errors

Error 3 Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. 0 0
Error 4 Errors in the OLAP storage engine: An error occurred while processing the 'Project Card' partition of the 'Project Card' measure group for the 'CDWH' cube from the CDWH OLAP database database. 0 0
Error 5 Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. 0 0

So it seems that's no good, This works perfect for dimension attributes but what am I missing here ?

Tx /Dirk

Found the solution

In the dimension usage tab, when you define the relationship. You can click the advanced button. In this dialog you can also modify the NullProcessing. So you need to set it here to UnknowMember instead of keyColumn of the dimension itself.

Wished MS would make it more consistent.

NullProcessing and Reference Dimensions

Have a Measure group in a fact table. This fact table links to the project dimension and the time dimension. In the project dimension there are some additional dates like ProjectStartDate and ProjectDueDate. So I create a new time dimension for these dates.

So for the Measure Group / Time (ProjectStartDate) combination, I define the dimension usage as a reference to the project dimension.

But when I process I get som errors on ProjectStartDate that date values (the NULL date) not exist time dimension (which is correct).

To solve this I try to set up the NullProcessing for these dimensions. But in contrast with a regular dimension there is no advanced section for reference dimensions.

Also the following don't solve the problem: setting NullProcessing on the ProjectStartDate attribute in the Project Dimension, setting NullProcessing on the Time dimension or setting NullProcessing on the project dimension.

Anyone know how to achieve NullProcessing:UnknownMember on a reference dimension ?

tx /Dirk

Try and solve this problem by defining Named Query in your DSV.

Replace your dimension table in DSV with Named Query that filters out NULL's.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

Filtering out the NULL's is not really an option since it would remove data from the cube.

I could fill in the NULL with dummy values (although for a time dimension this stays stange) but that's exactly what the NullProcessing should do. Put in a (black, zero, are UnknownMember) where there is a Null in the attribute or related key field.

For attibutes you specify it in the properties and this works fine.

For regular dimensions you specify this in the advanced options and works.

But what to do for a referenced dimension ? There is no such option, so I thought that specifying it in the referenced dimension might do the trick but it doesn't.

|||

You are right. In this case Analysis Server behaves differently.

But taking this aside. In general you are running into age old problem of having clean data and good referential integrity. Telling Analysis Server to hide some data that you have no keys for under Unknown member is not such a good practice. You will see your totals not summing up when try and sum child nodes manually. Strange things like that can create a perception with the user of Analysis Server showing incorrect data. You might be solving problem in short run by changing processing options, but in a longer run, you will be showing your users "inconsistent" data and that is in my opinion is quite dangerous

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks Edward,

Just thought that SSAS would make life a little simpler, Just have to get my ref-int scripts updated I guess. Still stupid that SSAS supports some cases but not all, which indeed makes it more or less useless.

/Dirk

Null values in cascading parameters

dear All,

I want to make a Report with attractive selection parameters.
That I mean, there is a parameter, Tparam (Time Param) which will activate other parameters (3 parameters: TimeA_1, TimeA_2 & TimeA_3. These selection values have been registered in the "Aviable values => non-queried".)

when Tparam selected TimeA_1 then Parameter for TimeA_1 will activated (and the others will be disappeared/hidden), vice versa for the other control (TimeA_2 & TimeA_3)

any idea to do that?

thank you...What you want is available, they are called Cascading Parameters, there is a tutorial here
http://msdn2.microsoft.com/en-us/library/aa337426.aspx|||

Hi SNMSDN,

In the link ,you sent i can't find any source regarding hiding or ignoring one parameter using another parameter.

Even i have another problem in setting null values . I have 5 cascading parameters ,level1,level2,level3,level4 and level5

when level1 is compulsory ,i had the remaining 4 parameters to accept for null and are made optional

for this i tried with "Allow null value option" in Report parameters but its not accepting

as an alternative i tried with default value 'Query based' to return null.

None of them directs me towards accepting null

It is aksing to select the parameter

It will be really a great help if some body faced some problem like this and resolved it

Thanks in advance

Raj Deep.A

|||

I have the same problem.

Basically, it appears that if you reference a parameter directly or indirectly via any other parameter's DS, then it will not respect the "Allow Null" setting for that parameter, since it thinks that it is implicitly required.

For example, I have a report with State & County parameters that both need to be set to enable "Allow Null". The County parameter references the State parameter's value within its Dataset, but no other parameter references the County parameter.

As a result, the County parameter respects the "Allow Null" setting, but the "State" parameter does not.

There MUST be a way around this restriction!!!

I have tried to hack around it by creating a dummy parameter called "StateFilter" that uses an expression to either return the Parameter!State.Value or Null, and then the County dataset references this parameter instead of the actual "State" parameter. However, this doesnt work either. Sql RS is "smart" enough to see the indirect reference and continues to treat State as a required parameter.

Help!!!

~Lance

|||

Nevermind.

I had forgotten about this wrinkle in how report parameters worked, but just now realized that you have to provide an option that has a value of NULL in order to select the "Allow Null" option. Makes sense when you think of it, but it would be nice if RS injected the NULL value option in such cases.

See this posting for the details:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1067464&SiteID=17

Null values in cascading parameters

dear All,

I want to make a Report with attractive selection parameters.
That I mean, there is a parameter, Tparam (Time Param) which will activate other parameters (3 parameters: TimeA_1, TimeA_2 & TimeA_3. These selection values have been registered in the "Aviable values => non-queried".)

when Tparam selected TimeA_1 then Parameter for TimeA_1 will activated (and the others will be disappeared/hidden), vice versa for the other control (TimeA_2 & TimeA_3)

any idea to do that?

thank you...What you want is available, they are called Cascading Parameters, there is a tutorial here
http://msdn2.microsoft.com/en-us/library/aa337426.aspx|||

Hi SNMSDN,

In the link ,you sent i can't find any source regarding hiding or ignoring one parameter using another parameter.

Even i have another problem in setting null values . I have 5 cascading parameters ,level1,level2,level3,level4 and level5

when level1 is compulsory ,i had the remaining 4 parameters to accept for null and are made optional

for this i tried with "Allow null value option" in Report parameters but its not accepting

as an alternative i tried with default value 'Query based' to return null.

None of them directs me towards accepting null

It is aksing to select the parameter

It will be really a great help if some body faced some problem like this and resolved it

Thanks in advance

Raj Deep.A

|||

I have the same problem.

Basically, it appears that if you reference a parameter directly or indirectly via any other parameter's DS, then it will not respect the "Allow Null" setting for that parameter, since it thinks that it is implicitly required.

For example, I have a report with State & County parameters that both need to be set to enable "Allow Null". The County parameter references the State parameter's value within its Dataset, but no other parameter references the County parameter.

As a result, the County parameter respects the "Allow Null" setting, but the "State" parameter does not.

There MUST be a way around this restriction!!!

I have tried to hack around it by creating a dummy parameter called "StateFilter" that uses an expression to either return the Parameter!State.Value or Null, and then the County dataset references this parameter instead of the actual "State" parameter. However, this doesnt work either. Sql RS is "smart" enough to see the indirect reference and continues to treat State as a required parameter.

Help!!!

~Lance

|||

Nevermind.

I had forgotten about this wrinkle in how report parameters worked, but just now realized that you have to provide an option that has a value of NULL in order to select the "Allow Null" option. Makes sense when you think of it, but it would be nice if RS injected the NULL value option in such cases.

See this posting for the details:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1067464&SiteID=17

NULL encountered in math

I need to subtract two values in a table. One of these values contains a
NULL entry. Each time I use the (Pledge - Credit) as 'Difference' I get the
value of NULL.
How do I get T-SQL to replace the NULL with zero?
I encountered this a long time ago while concatenating data. I was able to
use the command SET CONCAT_NULL_YIELDS_NULL OFF for strings. But I can't
find anything for numbers.
Thanks in advance
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:674BC95B-D16C-42B2-ACEA-7F8D799DD38E@.microsoft.com...
>I need to subtract two values in a table. One of these values contains a
> NULL entry. Each time I use the (Pledge - Credit) as 'Difference' I get
> the
> value of NULL.
> How do I get T-SQL to replace the NULL with zero?
> I encountered this a long time ago while concatenating data. I was able
> to
> use the command SET CONCAT_NULL_YIELDS_NULL OFF for strings. But I can't
> find anything for numbers.
> Thanks in advance
You can use COALESCE or ISNULL. Example:
COALESCE(pledge,0) - COALESCE(credit,0) AS difference
David Portas
SQL Server MVP