question

Maripili avatar image
Maripili asked

TSEQUAL & TIMESTAMP Lock too long the DB - SQL2000

Hi, I have a big question about this Function. Since I work in this company (two months ago) as DBA, I had to find the cause of the locks in the database. During this job, I found a query wich use TSEQUAL and takes too long to execute the process. The query is as follows: UPDATE XXXXXXXX SET XXXXXXXNumber = 431802 WHERE companyowner = '00000000' AND TypeDocument = 'TR' AND Series = '001' AND tsequal(Timestamp, 0x0000000011f13619) Before this work, I never used TSEQUAL, so i don't know if this sentence could takes lower time and how can I improve this. It would be really nice from you if you could explain me how this function works. Thanks a lot for your help! I really appreciate it.
sql-server-2000timestamp
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you very much for your answere @Sqlnubi, @oleg, @Trad, @willianD! @TRAD I saw the table, It isn't big and in there were added two indexes, It was like this: companyowner,TypeDocument,Series -> CLUSTERED companyowner,TypeDocument,Series -> NON CLUSTERED Twice the same fields...., but one was clustered and the other wasn't, is that correct? I think it isn't but may be I'm wrong. So in the NON CLUSTERED I recently add the "TIMESTAMP" field like your recomedation. But then I red the suggestion from @WilliamD, and I search for SARGability in GOOGLE, I found a really good documentation for my doubts -> http://explainextended.com/2010/02/19/things-sql-needs-sargability-of-monotonic-functions/ So I took out the TIMESTAMP field from that index.So I'm in the beginning. Rows Reserved Data Index Size Unused ---- ----------- ------- ------------- -------- 122 152 KB 64 KB 32 KB 56 KB Another think I noted, is that it takes time only if the user is executing an Update (billing) , but If I do it in a select sentence It doesn't takes much time. The update takes almost two minutes. What can I do? :( Thanks for your help & answeres! :)
0 Likes 0 ·
Maripali - the clustered index that you created should help on its own, in that it will allow you to filter down to the relevant information very quickly anway. How selective are the three columns (without Timestamp)? You can find this out by doing a count and comparing that to the count of the entire table: SELECT Companyowner,TypeDocument,Series,COUNT(*) FROM XXXXXXXX GROUP BY Companyowner,TypeDocument,Series If this is relatively low, then it may be enough to leave the clustered index as it is and live with the Clustered Index Seek that would occur. With 122 rows, 2 minutes for updating is awful! Are you experiencing blocking? Is the system highly concurrent?
0 Likes 0 ·
Thanks for your help @WilliamD, I did what you said. And it takes really nothing (miliseconds) ... so... I think that there's a normal concurrency (around 96 connections concurrently). When the users print bills, they experience slow process. This query blocks the other processes. So we have to wait that it ends. Therefore I want to improve this process... The Indexes were created before I came here. So do you recommend to delete de non clustered one, and let the clutered? The application is programmed in Power Builder, we have users who connect to this application trough terminal server. This blocks mostly come from users who are in the other regional offices and connect to the application through this ts. I though it was the TS but, it doesn't present a high activity during this blocks... Suggestions...??? Thanks again!
0 Likes 0 ·
You say it is slow when they print. Does the query take a long time to run (checking it by doing a trace) or is the application hung up while the printing is going on? The reason I ask, is if the users are connecting to the application through terminal server and are across a WAN, then it your issue may be the print job itself and have nothing to do with SQL. The query may be executing very quickly however it takes awhile for the print job to traverse the network.
0 Likes 0 ·
That was i also though. But first I wanted to be sure. I wannt to do more probes from TS. Thanks a lot for your help.
0 Likes 0 ·
Show more comments
WilliamD avatar image
WilliamD answered
I support the suggestion from Oleg (comment on TRADs answer) and that of sqlnubi about a possible index. Please also be aware that using a function in a `WHERE` clause or join will most likely make SQL Server unable to use your indexes as a function call negates SARGability. You will effectively force SQL Server to perform a table scan/clustered index scan for the table you are wanting to update. If the table you are updating is not very large, then this may not be a problem, but I suspect that this is exactly what you are experiencing as a problem. The index suggestion from sqlnubi may still not be of much help if the cardinality of the columns is not high enough. If the `WHERE` clause filters down to about 1% of the table contents, then it may be used by SQL Server to satisfy your query (and you use = instead of TSEQEUL), above that it may do a table scan anyway.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
tsequal just does a comparison of the timestamp to another. (time stamp equal) As for the long running query, it could be a number of things. To many indexes to have to update with the update statement, not the right index to support the where clause, millions of rows that it has to search through and compare by. You have lots of options to try to optimize this. If you could provide more information as to the size of the table and your index structure we could make some great recommendations.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@TRAD There is a rather convincing quote on connect site:
TSEQUAL has never been documented and was never supposed to be used outside of internally generated plans (server-side optimistic cursors) and DBLib. The fact that TSEQUAL was implemented using a keyword instead of using the framework that other builtin functions use was a mistake that caused the internal function hiding mechanism to be bypassed. This is something that dates back to the Sybase days and will hopefully change in a future version.
The same page has this from Erland Sommarskog:
TSEQUAL has never been documented with SQL Server. Use = instead.
Here is the link: http://connect.microsoft.com/SQLServer/feedback/details/126933/what-is-replacement-tsequal-function
2 Likes 2 ·
@TRAD Suppose I have a senior moment and don't remember what does **original_login()** do (just kidding of course, I will never forget this one). **Google** -\> **site:msdn original_login() sql server 2005** -\> **I am feeling lucky button** gives me the BOL reference for a desired version of SQL Server, and if it does not then I know that whatever I am looking for is not documented. First time I heard about **tsequal** was about 10 years ago at the end of my job interview when the guy interviewing me told me that I am hired, but if I ever heard about **tsequal** then I need to know that I can never use it. I never heard of it, so never using it was perfectly cool with me :)
1 Like 1 ·
@OLEG, thanks for the input. I had never seen or heard of tsequal before this post and had to do a quick search on it myself. I like @WillaimD reponse to about @Maripilli using this in a function will negate any usage of a composite index as mentioned by @sqlnubi. I learn so much by reading all these posts everyday. It seems that @Maripilli should rewrite the function into a stored procedure and use = instead of tsequal and build an index to support the sproc if one doesn't already exist to satisfy the requirement.
0 Likes 0 ·
sqlnubi avatar image
sqlnubi answered
How long is this running and how often does it run. If it runs many times per day you might consider a composite index on companyowner, typedocument, series, and timestamp.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.