SQL Server Perfomance issue

Question

SQL Server Perfomance issue

Ryk Neethling

Old Hand

Points: 380
More actions
January 5, 2005 at 9:47 pm

#87260

Hello,
I have just started monitoring a new site and they have several performance issues, and it has stumped me.
Firstly they use a lot of stored procedures that creates temp tables, but they create the temp tables in the following way:
Declare @tmpUserAccounts table (ID int IDENTITY (1, 1),
user_id_int int ,
accnt_id int,
user_role nvarchar(10) COLLATE database_default ,
user_access nvarchar(10) COLLATE database_default,
adviser_id int)
As oppose to the way I am used to:
create table #tmpUserAccounts .....
They never drop those tables, and I find that you cannot drop them either.
The problem that occurs is that the pagefile grows to its maximum and then when you restart sql service the pagefile drops to minimum again and then just builds up again. I know that tempdb gets flushed when the service is restarted, but I cannot find any of those objects in tempdb.
Any help or suggestions would be greatly appreciated.
Thank you

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

@tmpUserAccounts is being declared as a local variable, with a datatype of table. So it is different from a temporary table. It should therefore be possible to SET the variable to NULL to free up memory.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Antares686 SSC Guru Points: 125444 More actions · Answer 2

A table variable only exists during the run of the process that created it as is immediately destoryed at the end and any pages in tempdb associated with it are freed.

Basically each time the procedure completes execution the object is immediately destroyed.

So unless the stored procedures that create them are looping back on themselves internally and never ending they are destoryed each time.

It may not be these objects that are your performance hit unless they are extremely large in that case you might want to change the code over to a temp table instead to see the impact.

But you cannot set them to null and you cannot truncate the data from the object just DELETE or allow it to free itself.

Ryk Neethling Old Hand Points: 380 More actions · Answer 3

Ok, that is agreed and that is what I thought, but why is SQL using so much pagefile memory. The site have an overnight batch process that runs each night and it is very disk intensive processing on the database, because it rebuilds a couple of tables.

Basically what I would like to know is why is sql server occupying 1.5 gig in task manager and my pagefile sitting at 1.8 gig and when I restart the service that seems to clear itself out to be normal sizes, and then it just start building up from there again.

I had a look at some performance counters on that server and the pages/sec is resonable, it hovers around 5-10 and occasionally shoots up tp 80-90 and then straight back down again.

Any ideas would be appreciated.

Thanks

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Then you have either a lot of data, and use of Table variables can be related, in memory at one time or you don't have enough memory to support it as it is now anway. How much memory does your sever currently have total and what version of OS and SQL edition are you running?

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 5

I had a problem recently with an SP using table variables running slowly in production when it had been OK in development. The difference turned out to be that the production server was mutli-processor and the development server was single. I found a post on the web explaining that table variables can't make use of a multi-processor environment and it messes up the query optimisation. Changing to temporary tables dramatically improved performance.

Russell Shilling SSCarpal Tunnel Points: 4349 More actions · Answer 6

SQL Server will, by design, use up gobs of RAM -- as much as it can get. It should free up resources if other apps need it. I wouldn't dwell on the 1.8 Gig RAM in Task Manager, as that sounds like normal SQL behavior.

The Page File size issue could be caused by something else, such as a text import/BCP program running that needs lots of RAM?

So long, and thanks for all the fish,

Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

Ryk Neethling Old Hand Points: 380 More actions · Answer 7

How could I eliminate the cause of the pagefile being overloaded. My perf counters does not show a big number of page faults, only the occasional one.