| Author |
Topic |
|
falcon-IT
Starting Member
8 Posts |
 Posted - 2009-01-26 : 17:12:29
|
| Hello,Need some help here. I have been troubleshooting issue on our SQL server that is driving me nuts.I have a DELL POWEREDGE 2600 INTEL XEON 3GHZ, with 3GB RAM and 2 146GB 15.5K drives setup in RAID1 Mirror and SQL 2000 sp4 that is used by the Escrow transaction software also installed on the server.On the server, when i open the app and switch between escrow transactions, there is NO DELAY. But on ALL the 10 workstations, when a user FIRST TIME opens the ESCROW program they have to wait 25sec (i counted it) for it to open then they can work fine and work within that particular tranasction...UNTIL they switch to another ESCROW tranaction, they have to wait 25sec for that to open also. It is very frustrating for the users and I have tried to work with the Escrow Software tech support and they have stated, it could be the network and everything but not them. I checked all the router/firewall /switch settings and don't see anything that would cause the issue. It happens on ALL the workstations.I have the same setup in 3 other offices without any issue! The main difference here is that i had to reconfigure the server with a 3 drive RAID-5 few months back and REINSTALLED everything. Including SQL2000 and the Escrow software. I though t it was the RAID 5 setup since the striping effects the database performance (although it not a huge database) so last week i reconfigured back to RAID1 (mirror).Is there a delay setting or a tweak that i can check to fix this issue? Should i recheck with the escrow software company? Help!!! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
falcon-IT
Starting Member
8 Posts |
Posted - 2009-01-26 : 17:46:06
|
quote:
Originally posted by tkizer
Run SQL Profiler to determine if there is a long-running query associating with that issue.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Thanks for the quick response Tara, I truly appreciate it.I am not as experineced in SQL as I mostly manage the network and user tech support aspect of the company so you may have to take my hand and walk baby steps with me when it comes to SQL  I do see the profiler in SQL 2000, what sort of exact template am i running?Thanks Again |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
falcon-IT
Starting Member
8 Posts |
Posted - 2009-01-26 : 18:12:11
|
quote:
Originally posted by tkizer
Just use the default template, save the data to a table, duplicate the 25 seconds issue, stop the trace, and then run this query:SELECT TOP 1000 Duration/1000 AS DurationInSecs, TextDataFROM YourTraceTableWHERE TextData IS NOT NULLORDER BY Duration DESCOnce you query it, do you see one taking more than 20 seconds?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
I apologize for asking simple questions and I understand what you want me to do but i wanted to make sure about few things:-When saving as a table, i get options for the default databases and the Escrow program databases? What am i attaching it to?-Should the users not be accessing the data when I am doing this?-any precaustions?Please bear with me...I am almost there. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-01-26 : 18:27:30
|
| Just store it somewhere, it doesn't matter where. I typically put my traces into an Admin or DBA database. The users can definitely be accessing the system while you are doing this. SQL Profiler can cause performance issues, especially when saving it to a table. I'd recommend a trace file instead, but that complicates this too much for someone not comfortable with SQL yet. As soon as you've duplicated the issue in the application, stop the trace so that it stops collecting data.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
falcon-IT
Starting Member
8 Posts |
Posted - 2009-01-27 : 01:42:50
|
quote:
Originally posted by tkizer
Just store it somewhere, it doesn't matter where. I typically put my traces into an Admin or DBA database. The users can definitely be accessing the system while you are doing this. SQL Profiler can cause performance issues, especially when saving it to a table. I'd recommend a trace file instead, but that complicates this too much for someone not comfortable with SQL yet. As soon as you've duplicated the issue in the application, stop the trace so that it stops collecting data.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Tara,I was still not sure to what database I should insert the trace table on since i had master, northwood, pubs and then 10 escrow software related databases. I was so worried that i will mess up the databases so to be safe i ran a trace file, recreated the delay issue and saved it on the server. I am looking at it...what should i pay attention to? Can i post the " .trc" trace file here? |
|
|
|
falcon-IT
Starting Member
8 Posts |
Posted - 2009-01-27 : 01:57:22
|
quote:
Originally posted by falcon-IT
quote:
Originally posted by tkizer
Just store it somewhere, it doesn't matter where. I typically put my traces into an Admin or DBA database. The users can definitely be accessing the system while you are doing this. SQL Profiler can cause performance issues, especially when saving it to a table. I'd recommend a trace file instead, but that complicates this too much for someone not comfortable with SQL yet. As soon as you've duplicated the issue in the application, stop the trace so that it stops collecting data.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Tara,I was still not sure to what database I should insert the trace table on since i had master, northwood, pubs and then 10 escrow software related databases. I was so worried that i will mess up the databases so to be safe i ran a trace file, recreated the delay issue and saved it on the server. I am looking at it...what should i pay attention to? Can i post the " .trc" trace file here?
ok..so i saved the trc file...I see i have the option of saving the file as a trace table and placing it in a database (i still worry i will mess up the database if i do that)... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
falcon-IT
Starting Member
8 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
falcon-IT
Starting Member
8 Posts |
Posted - 2009-01-27 : 17:32:08
|
quote:
Originally posted by tkizer
There shouldn't have been any NULLs for TextData if you used my query:SELECT TOP 1000 Duration/1000 AS DurationInSecs, TextDataFROM YourTraceTableWHERE TextData IS NOT NULLORDER BY Duration DESCSo with the above query, the very first row shows 0 for DurationInSecs column?Also, you reproduced the 25 seconds delay while the trace was running, right?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Yes I did use your Query to get the results.Through profiler i created a Trace and used the STANDARD profile trace, saved as a table in a manualy created database, Opened up Query in that database, copied and pasted you query and changed the "yourtracetable" to the table name i created in trace. I re-ran it and got the same results minus now there were more rows. Very first row is 0 and i did recreate the 25sec delay.I can send you the output. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
falcon-IT
Starting Member
8 Posts |
Posted - 2009-01-28 : 13:08:16
|
quote:
Originally posted by tkizer
Then the issue isn't inside SQL Server. The 25 seconds delay is caused by something else.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
The problem is fixed!!!I'll try to explain this as best as I can and keep it short.The Escrow program creates REGISTRY keys in Windows that correspond to the OFFICE# that a user picks when the program starts which links that OFFICE# to a database in SQL.This office ONLY had OFFICE1. And had the SQL databases linked to OFFICE1. For reasons me and the escrow tech support still does not know, there was a OFFICE2 register key in windows registry right under the OFFICE1 key. This particular key had irrevelant data and strings pointing to an older server and SQL databases that were detached while back. When the program would start it would see that there was an OFFICE2 but then, i am guessing, it would delay to get to the actual databases corresponding to OFFICE1.Deleted the OFFICE2 key...ALL GOOD!!! NO DELAY WHATSOEVER!!!...Thank you so much Tara...i really appreciate your help.. I have learned so much about SQL just by your guidance.Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
25 SEC Delay