0

I had a logs database with over 3 million rows. Apparently it was responding slow and hence I decided to truncate the tables.

I couldn't truncate the tables as sql management studio was crashing when I tried to delete foreign key constraints before truncating. Hence I DELETED the database and ran the script again to create the database and tables. To delete the database I just right-clicked on the database and hit Delete option.

Even after that, I see that the SELECT/INSERT operations are taking as long as it took before. So I went ahead looked for Ghost records, but couldn't find any. May be that's because I had already re-created the database.

Still I went ahead and forced the Ghost cleanup.

DBCC ForceGhostCleanup;
GO

I am yet not able to improve the performance. What can I do to resolve the issue?

Thank you.

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
Marshal
  • 6,551
  • 13
  • 55
  • 91
  • 3
    Well 3 millions of row is nothing for a db. And if you have problems there are other things you should check first, memory, hard disk, index, concurrency. But delete the database was overkill. Is like you car didnt start in the morning and you go and buy a new one :/ – Juan Carlos Oropeza Dec 01 '16 at 16:49
  • @JuanCarlosOropeza: I definitely agree that I took an extreme step, but I did it because the data wasn't important. But I'll keep that in mind. I have more than enough free memory 12GB and hard-disk is 1TB, so that shouldn't be a problem. I think what I need is clustered indexes for better performance. – Marshal Dec 01 '16 at 16:53
  • Take a look at my answer, its in the same line of thought.. @Marshal – Neo Dec 01 '16 at 16:54
  • 2
    again 3 millions is nothing,. I have 40 millions record every month and only 2 GB ram. You should check what query is slow by looking the db execution plan. http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan – Juan Carlos Oropeza Dec 01 '16 at 16:56
  • @JuanCarlosOropeza: I mean even if I right click on the table and select "Edit TOP 200 Rows", it takes a while to execute, although database is currently empty. – Marshal Dec 01 '16 at 16:59
  • That usually is either memory, disk/index fragmentation, or cpu. Look at hardware related statistics in performance monitor. @Marshal – Neo Dec 01 '16 at 17:00
  • that looks like a hard disk problem, happen the same if you create a new table? but then your question is too broad. `Apparently it was responding slow ` doesnt say anything. Instead of say I have a 3 millions db delete and recreate, just ask `'why edit top 200 take forever'`. See the difference? First is too broad, second we can focus on the problem. – Juan Carlos Oropeza Dec 01 '16 at 17:03
  • @JuanCarlosOropeza Yes it happened even if I re-created the database, but what surprises me is that it happens with just that particular database and not with any other databases on the same sql instance. – Marshal Dec 01 '16 at 17:06
  • 1
    As Juan Carlos wrote 3 million records isn't a lot, however it can be if you are dealing with large rows [e.g. lots of varchar(max) nvarchar(max), varbinary(max) etc.] Anyway, there some good suggestions here about rebuilding indexes etc but you could simply have a missing index or potentially an error in your insert statement that is duplicating rows by causing a cross join of millions of records or.... the best way to get help from us on this would be to include your query and an execution plan. Otherwise we are flying blind to what you need to do. – Matt Dec 01 '16 at 17:11
  • @MisterPositive JuanCarlosOropeza You guys were correct, sqlserver was eating up 94% of memory. When I restarted the sql server, it the memory usage dropped to 9%. – Marshal Dec 01 '16 at 17:18

2 Answers2

1

Did you look at any existing index fragmentation? You probably need to rebuild or reorganize your indexes.

This article spells out the process here: MSDN Index rebuild or reorg

Here is a sample reorg statement:

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee  
REORGANIZE ;

Here is a sample rebuild statement:

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;

You can also do this type of maintenace using a database maintenance plan, which can be implemented easily with a wizard like interface.

Neo
  • 3,309
  • 7
  • 35
  • 44
  • @JuanCarlosOropeza I don't follow. This is an answer, to a broad question I will admit. – Neo Dec 01 '16 at 16:51
  • Sorry, I was confused by the avatar, tought was original OP. Also both name start with M. – Juan Carlos Oropeza Dec 01 '16 at 16:52
  • @MisterPositive I'll look into it and get back to you. As I am no database expert, I may take some time to revert back. – Marshal Dec 01 '16 at 16:55
  • 1
    No Hurry @Marshal . I am here to help when I am free too. That article will help you look at your DB as a whole from this perspective. You can also do a maintenance plan on your DB and account for this automatically. – Neo Dec 01 '16 at 16:58
0

Working on comments provided by Mister Positive and Juan Carlos Oropeza, I found that SqlServer was taking 94% of the memory on my PC, even if I deleted the database, it didn't free up the memory (not the disk space).

To resolve this I had to just restart SqlServer which brought back the memory consumption to just 9% and everything was smooth then after.

Marshal
  • 6,551
  • 13
  • 55
  • 91