Looks like the developer was trying to list the index columns, but I don't think that's the right way. There's a much easier (faster) way in 2005, but if you...

6 million rows at say an average of 20 characters each (rough guess) means that query is pulling around 200 MB of data from the database and across the network....

Since there's no where clause, SQL has to read all the rows of the table. Indexes aren't going to help much. At best, you could change the exec plan from...

3-4 seconds or...

I'm not sure about speeding the query up, it's based off system tables and system views which you cannot change.

That said, 50 indexes and 230 statistics on a single table?...

What's the security settings on the linked server "srvr1"?

(In object explorer, Server object -> Linked servers, right click that linked server, choose properties, go to the security tab)

PhilM99 (8/13/2008)

---

OK, will do that. I access the server remotely through Remote Desktop, so I as an admin authenticate through Windows. Sounds like a good idea to disable sa completely....

Have you considered using FullText Search? This isn't an area that the relational engine excels in.

TEXT is an older datatype, is deprecated in SQL 2005 and will be removed in a future version. Many of the string functions (left, right, substring, replace, etc) don't work...

Desert camel rides in Mauritius? Dubai, I could see, but Mauritius is a small tropical island. The only...

What does the procedure "ap_mtn_Sync_Table_data" do, and what are the security settings of the linked server that it is using?

In object explorer, right click the login, select properties, go to status, select disabled, click OK.

Depending where the server is and how you're accessing it, I do prefer windows authentication...

Mike Alfa (8/13/2008)

---

Going with your profiler suggestion, let me what key areas or counters I should monitor or can you give me some references or articles so that I can...

No need to start a second thread.

Please ditrect replies to the following thread, to keep all the discussion together

http://www.sqlservercentral.com/Forums/Topic551587-360-1.aspx

SQL Server has a built-in deadlock detector. When it notices a deadlock it will automatically select one of the processes involved in the deadlock and kill it. There's no need...