question

anandnarayanan avatar image
anandnarayanan asked

DataBase is performance is very SLOW

I **have one databse named XPstream( which is of 50 GB ) which is good in performance wise. however i have created new another database called "XPstreamqa"(with recovery model SIMPLE) using XPstream's Backup. after restoring the database, i am facing lot of performance issues. for example in XPstream , Select * into from a linkedserver table executes 5 minutes( linked server's table is huge in size!!). where as in XPstreamqa, the same query getting executed more than 2 hours. i dont have clue what is wrong on my new database(XPstreamqa). Any suggestions/ help is much appriciated.**
performancedatabase-design
10 |1200

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

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
It could be any number of things. Linked server queries are notoriously slow because they have to move data across the network and frequently can't take advantage of indexes (although a SELECT * without a WHERE clause isn't going to use an index any way). To know why things are running slow, one of the best places to start is with wait statistics, sys.dm_os_wait_stats. That Dynamic Management View (DMV) will show you what the server is waiting on. You can query it before and after a you run a query to get an idea of what a given query is waiting on. But, that's just the start. Performance tuning SQL Server is a long and involved process. I'd suggest getting a [copy of my book][1] to learn more. [1]: http://www.amazon.com/Server-Query-Performance-Tuning-Experts/dp/1430242035/ref=sr_1_1?ie=UTF8&qid=1408444916&sr=8-1&keywords=fritchey+query
10 |1200

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.