Latch Time

Hi All,

This might be a bit of a question like "how long is a peice of string" however here goes....

What is considered to be a high average latch time?

We have a SQL2000 machine which has:
700-900 ms average latch time
100% Buffer Cache Hit Ratio
No memory pages / second
Very low (almost always below 5%) cpu usage
Next to no disk transfers / sec
0 average lock time

I am surprised that the latch time is so high. Any ideas, whether I should be looking at something in particular?

Cheers in advance.
TroyAnyone - please?|||It really is kind of a hard question to answer. :) A high latch time generally points to a memory shortage and has a direct correlation to a low buffer cache hit ratio. Since your buffer cach hit ratio is 100%, you obviously don't have a problem with that.

The other thing it can point to is needing more I/O width on your disk subsystem. That's pretty doubtful again with that high of a buffer cach hit ratio. You might want to look at your physical read and write cache to see what those counters are at.

Really though, it doesn't seem to me you have any problems and I wouldn't worry about the 700-900ms average latch time. If you see that number rising dramatically when a load gets put on the system, you need to come back and add to this post so we can look at it. Otherwise, I wouldn't worry about it, especially if your system seems to be running fine and getting the desired response times from the database server.

Hope this helped.|||Thanks for the response!! Yeah that is pretty much what I had thought - but was after some confirmation from others.
Had a feeling it was slow disk/issues with I/O, so will keep an eye on it and if it causes any more concern, I will post back.

Thanks again!
Troy

last/first name string

I need to seperate a last name, first name string. I am migrating data from FoxPro then merging it with data that has been migrated from Paradox. In Paradox the name is kept in: "last, first" format. I need to reformat the name to be: first(space)last. Suggestions...Based on the assumption that the first comma in the string marks the end of the last name, you could use
SELECT @.comma = CharIndex(',', @.paradoxName)
SELECT @.last = LTrim(RTrim(Left(@.paradoxName, @.comma - 1)))
SELECT @.first = LTrim(RTrim(SubString(@.paradoxName, 1 + @.comma, 8000)))-PatP|||select reformatted_name = replace(paradox_name, ', ', ' ') from paradox_table where charindex(', ', paradox_name) > 0|||Originally posted by rdjabarov
select reformatted_name = replace(paradox_name, ', ', ' ') from paradox_table where charindex(', ', paradox_name) > 0

This takes care of the comma but doesnt solve the position of the names. Thanks though...|||select reverse(substring(reverse('last, first'), 1, charindex(', ', 'last, first'))) + ' ' +
substring('last, first', 1, charindex(', ', 'last, first')-1)|||Just advice - keep last and first names in different fields.|||Originally posted by snail
Just advice - keep last and first names in different fields. I'll drink to that!|||I was kidding - April 1 ;)|||Originally posted by Pat Phelan
Based on the assumption that the first comma in the string marks the end of the last name, you could use
SELECT @.comma = CharIndex(',', @.paradoxName)
SELECT @.last = LTrim(RTrim(Left(@.paradoxName, @.comma - 1)))
SELECT @.first = LTrim(RTrim(SubString(@.paradoxName, 1 + @.comma, 8000)))-PatP

Pat,
this worked like a charm. Thanks a million.|||Pat does strings...|||Originally posted by rdjabarov
select reverse(substring(reverse('last, first'), 1, charindex(', ', 'last, first'))) + ' ' +
substring('last, first', 1, charindex(', ', 'last, first')-1) You really are evil, but I think you'd already figured that part out!

-PatPsql

last position of the specified expression in a character string

Hi,
could you help me. I'd like to get last position of the specified expression in a character string(a fast solution for DB)
Thanks for youe helpcould you give an example please? which specified expression? what character string?|||This will probably involve some creative use of the REVERSE function, but give us some more details, please.|||yeah, REVERSE, that's what i was gonna say, with actual sql :D|||Darn, I was going to suggest Reverse() and Patindex() :(|||yeah, with one invokation of DATALENGTH() as wellsql

last index of character in string?

hello everyone.
i've got a "name" column that i would like to split into "firstname"
and "lastname" columns. the firstname was no problem (the values have
already been LTRIM and RTRIM'd):
UPDATE test
SET firstname = LEFT(name, CHARINDEX(' ', LTRIM(name), 1) - 1)
however i'm curious about how to do the last name. you see, there might
be an arbitrary number of ' ' characters in the "name" column values
(in fact the max is seven). however regardless of how many spaces
occur, or what's inbetween those spaces.
now, i can set up a very complicated nested CHARINDEX/SUBSTRING
analysis for each number of ' ' characters that occur, starting with
the seven space condition, and working my way down. but good grief that
would be complicated.
is there any nifty tricks to find the LAST index of a specified
character in a string, by chance? that would save me a world of hurt.
thanks!
jason> is there any nifty tricks to find the LAST index of a specified
> character in a string, by chance? that would save me a world of hurt.
How about CHARINDEX(' ', REVERSE(TRIM(name)))
However saving you even more of a world of hurt (e.g. not changing the names
of people like Larry da Costa and Filipe de Simone), you should just grab an
off-the-shelf package for data scrubbing. These have much more advanced
rules, and a much higher success ratio, than anything anyone will be able to
write in T-SQL alone.
A|||Try using reverse to flip the string around
UPDATE test
SET lastname = LEFT(name, CHARINDEX(' ', LTRIM(REVERSE(name)), 1) - 1)
Archer
"jason" wrote:

> hello everyone.
> i've got a "name" column that i would like to split into "firstname"
> and "lastname" columns. the firstname was no problem (the values have
> already been LTRIM and RTRIM'd):
> UPDATE test
> SET firstname = LEFT(name, CHARINDEX(' ', LTRIM(name), 1) - 1)
> however i'm curious about how to do the last name. you see, there might
> be an arbitrary number of ' ' characters in the "name" column values
> (in fact the max is seven). however regardless of how many spaces
> occur, or what's inbetween those spaces.
> now, i can set up a very complicated nested CHARINDEX/SUBSTRING
> analysis for each number of ' ' characters that occur, starting with
> the seven space condition, and working my way down. but good grief that
> would be complicated.
> is there any nifty tricks to find the LAST index of a specified
> character in a string, by chance? that would save me a world of hurt.
> thanks!
> jason
>|||Jason,
Could you use something like this'
declare @.name varchar(50),
@.FirstName varchar(25),
@.lastName varchar(25)
Set @.name = 'Joe Bloggs'
Set @.firstname = ltrim(rtrim(LEFT(@.name, CHARINDEX(' ', LTRIM(@.name),
1) - 1)))
Set @.LastName =ltrim(rtrim(Right(@.Name, (Len(@.Name) -
Len(@.FirstName)))))
select @.name, @.FirstName, @.lastName
HTH
Barry|||Sorry, it probably should be
UPDATE test
SET lastname = LEFT(REVERSE(name), CHARINDEX(' ', LTRIM(REVERSE(name)), 1) -
1)
Archer
"bagman3rd" wrote:
> Try using reverse to flip the string around
> UPDATE test
> SET lastname = LEFT(name, CHARINDEX(' ', LTRIM(REVERSE(name)), 1) - 1)
> Archer
> "jason" wrote:
>|||> How about CHARINDEX(' ', REVERSE(TRIM(name)))
by TRIM( I meant LTRIM(RTRIM(, of course. Not all of us create a function
called TRIM(). :-)|||AHA! *REVERSE* that's exactly what I was hoping existed. Took a little
tweaking like so:
UPDATE test
SET lastname = REVERSE(LEFT(REVERSE(name), CHARINDEX(' ',
REVERSE(name), 1) - 1))
But that worked like a charm! Thanks very much.
As for the value of the data, it's not worth any scrubbing of note, but
I do appreciate the suggestion for cases that might be.
Jason

Largest string for execute

Hello there
I have function that build sring to execute.
The function create string on 6000 characters size.
Because it's size i had to build it with two strings
How can i execute this size of string?
any help would be usefulYou should be able to execute strings like this:
declare @.sql1 varchar(8000),@.sql2 varchar(8000)
set @.sql1=dbo.your_udf(para1)
set @.sql2=dbo.your_udf(para2)
exec(@.sql1+@.sql2)
-oj
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:OKCY8OSAFHA.3424@.TK2MSFTNGP11.phx.gbl...
> Hello there
> I have function that build sring to execute.
> The function create string on 6000 characters size.
> Because it's size i had to build it with two strings
> How can i execute this size of string?
> any help would be useful
>

Large String Parameters Causing Performance Issues

Hi All,
Bit of strange one this. I have a key field in one of my databases that is a
Char(18). I use this as a parameter field in one of my reports.
When I run the report and specify the key field parameter manually the
report runs instantly. If I attempt to set this as a parameter variable and
enter the criteria in the parameter box, the report takes over a minute to
run. The 2 statements are as follows.
1) select sum(sales_value) as sales from production.dbo.epos_transactions
where transaction_ref = '085505010500074995'
When run as above, the report runs instantly.
2) select sum(sales_value) as sales from production.dbo.epos_transactions
where transaction_ref = @.TREF
When I enter the parameter and run the report it takes over a minute to run.
Running the query through query analyser causes no issues on any of the
servers involved and the response time on all servers is instant.
I'd be grateful if anyone can shed any light on this.
Many thanks for any help.
--
Nick Colebourn
DBA
United Coop LtdHi Nick,
Could you supply a generic repro for this issue. An RDL for Northwind or a
simple DB. I would love to take a look at it.
| Thread-Topic: Large String Parameters Causing Performance Issues
| thread-index: AcUC1GzdZAw/tJ+RRciPnzP3TypLNA==| X-WBNR-Posting-Host: 213.146.135.115
| From: "=?Utf-8?B?TmljayBDb2xlYm91cm4=?="
<NickColebourn@.discussions.microsoft.com>
| Subject: Large String Parameters Causing Performance Issues
| Date: Tue, 25 Jan 2005 03:53:03 -0800
| Lines: 29
| Message-ID: <EFE8F146-E9CC-49C8-A5B8-4E29E3F57EC2@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:40737
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi All,
| Bit of strange one this. I have a key field in one of my databases that
is a
| Char(18). I use this as a parameter field in one of my reports.
| When I run the report and specify the key field parameter manually the
| report runs instantly. If I attempt to set this as a parameter variable
and
| enter the criteria in the parameter box, the report takes over a minute
to
| run. The 2 statements are as follows.
|
| 1) select sum(sales_value) as sales from production.dbo.epos_transactions
| where transaction_ref = '085505010500074995'
|
| When run as above, the report runs instantly.
|
| 2) select sum(sales_value) as sales from production.dbo.epos_transactions
| where transaction_ref = @.TREF
|
| When I enter the parameter and run the report it takes over a minute to
run.
|
| Running the query through query analyser causes no issues on any of the
| servers involved and the response time on all servers is instant.
|
| I'd be grateful if anyone can shed any light on this.
|
| Many thanks for any help.
|
| --
| Nick Colebourn
| DBA
| United Coop Ltd
||||Hi Brad,
Many thanks for your reply. I'll see if I can get it to do the same
with a northwind query and post back asap. For your info, I've created an
in-line table valued function containing the sql with the parameter and
called this as the dataset instead of the sql text itself. This works ok
either with static criteria or using a parameter passed across to the
function. The performance issue only seems to happen when you pass the
parameter value directly across to the sql text.
Many thanks
Nick
""Brad Syputa - MS"" wrote:
> Hi Nick,
> Could you supply a generic repro for this issue. An RDL for Northwind or a
> simple DB. I would love to take a look at it.
>
> --
> | Thread-Topic: Large String Parameters Causing Performance Issues
> | thread-index: AcUC1GzdZAw/tJ+RRciPnzP3TypLNA==> | X-WBNR-Posting-Host: 213.146.135.115
> | From: "=?Utf-8?B?TmljayBDb2xlYm91cm4=?="
> <NickColebourn@.discussions.microsoft.com>
> | Subject: Large String Parameters Causing Performance Issues
> | Date: Tue, 25 Jan 2005 03:53:03 -0800
> | Lines: 29
> | Message-ID: <EFE8F146-E9CC-49C8-A5B8-4E29E3F57EC2@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:40737
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hi All,
> | Bit of strange one this. I have a key field in one of my databases that
> is a
> | Char(18). I use this as a parameter field in one of my reports.
> | When I run the report and specify the key field parameter manually the
> | report runs instantly. If I attempt to set this as a parameter variable
> and
> | enter the criteria in the parameter box, the report takes over a minute
> to
> | run. The 2 statements are as follows.
> |
> | 1) select sum(sales_value) as sales from production.dbo.epos_transactions
> | where transaction_ref = '085505010500074995'
> |
> | When run as above, the report runs instantly.
> |
> | 2) select sum(sales_value) as sales from production.dbo.epos_transactions
> | where transaction_ref = @.TREF
> |
> | When I enter the parameter and run the report it takes over a minute to
> run.
> |
> | Running the query through query analyser causes no issues on any of the
> | servers involved and the response time on all servers is instant.
> |
> | I'd be grateful if anyone can shed any light on this.
> |
> | Many thanks for any help.
> |
> | --
> | Nick Colebourn
> | DBA
> | United Coop Ltd
> |
>

Large string data

I have a web site that allows user to enter large strings into a database (comments, etc). What is the best way to do that? Right now I have them limited to 25 characters and the data type is varchar. Is there a better way?
Thanks!You may consider using OPENXML.|||VARCHAR can hold up to 8000 characters. 25 characters is too small for the comments. If you do not want to let user enter more than 8000 characters in the comments, it is better to use VARCHAR. Otherwise, you may change to TEXT. Note that TEXT datatype sometimes cause the problem.

large size of string in the drill down

Hello,

I have a issue with the drill down. In the report there is drill down in the Amount column. I am trying to pass the customer names in this drill down but there are more than 100 customers for that specific case and drill down is not able to pass all the customers.

Is there any other way to pass the large string in the drill down?

You may be able to write some custom code that would compress the string and then uncompress in the drill down.

Here is some example code.

http://www.developerfusion.co.uk/show/1642/

Otherwise try and pass keys instead of names into the drilldown.

cheers,

Andrew

|||Can someone please help on this?

Large Parameter Sring

I am using a string for the querystring of a report, and I am passing in parameters to substitute values. However, one of the parameters is over 1200 characters long and when I run the report from the designer it executes, but when I run it from report manager it doesn't run the report, and I don't receive any errors, and nothing shows up in the error logs. Please let me know if there are some kind of limitations on the amount of data a parameter can pass in a string data typeYou are probably hitting this URL limitation:
http://support.microsoft.com/support/kb/articles/Q208/4/27.ASP
Currently, there is no workarond.
--
This posting is provided "AS IS" with no warranties, and confers no rights
"pietrzbm" <pietrzbm@.discussions.microsoft.com> wrote in message
news:62AB9382-CCD8-466C-A36A-71462D174638@.microsoft.com...
> I am using a string for the querystring of a report, and I am passing in
parameters to substitute values. However, one of the parameters is over
1200 characters long and when I run the report from the designer it
executes, but when I run it from report manager it doesn't run the report,
and I don't receive any errors, and nothing shows up in the error logs.
Please let me know if there are some kind of limitations on the amount of
data a parameter can pass in a string data type