hi there, when I'm checking the sysprocesses table, most of the time I see lastwaitype PAGELATCH_UP and waitresource 2:1:112 which is the sysindexes table in tempdb. is it perfectly normal or I should do something about it. thanks
What does sp_lock look like? Do you have performance problems? If not, don't fret. Tom Pullen DBA, Oxfam GB
I knew we don't have locking/blocking issue here. deadlocks hardly happen since we migrated to SQL 2000. thanks!
Page latch update relates to contention for allocation of related pages. The contention indicates more data files are needed. This KBAhttp://support.microsoft.com/default.aspx/kb/328551 refers more information. Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
thanks for your links.<br />I think it is caused by temp tables with indexes created by sprocs (who will create an index on a small temp table???). user objects donot show up on the table (nor on dbcc page result), so donot know if it is right or not.<br />[<img src='/community/emoticons/emotion-1.gif' alt='' />]
So do you think the application creates such indexes on temp tables, it is good thing for performance but not always. Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
I was surprised that I couldn't find created index statement from our stored procedures/triggers (app based).<br /><br />have you guys noticed any 2:1<img src='/community/emoticons/emotion-12.gif' alt=':x' />xx waits? ( excluding 2:1:1 or 2:1:3 )<br />
If the temp table is created by defining primary key, the SQL will create an index on that Pkey column by default. In one article Kalen referred: I also introduced the undocumented DBCC EXTENTINFO command. This command can show you which extents SQL Server has allocated to an object as uniform extents (eight consecutive pages) and which pages it allocated from mixed extents—that is, one page at a time. Knowing when and how often SQL Server allocates mixed extents can help you determine whether mixed-extent allocation is causing concurrency problems. Let's examine the reasons that too many mixed-extent allocations can be a problem, then look at several possible solutions. However, single-page allocation has a drawback. If you're creating many small tables concurrently, you can encounter a special kind of contention that can be hard to troubleshoot. Brian Moran discussed this problem in his October 23, 2003, SQL Server Perspectives column, "Is Your Tempdb Stressed Out?" (InstantDoc ID 40615). Moran described a situation in which many users were running a stored procedure that created tens of thousands of temporary tables quickly in a client's tempdb database. As I mentioned last month, each object—including temporary tables—needs to allocate two pages from mixed extents when it gets its first row, so in this situation, you have tens of thousands of simultaneous requests for page allocation. Refer to the SQL Server magazine for referred instant doc. for more information (I think that article will be marked for subscribers only). HTH Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
You are right Satya - primary key is also one thing to check. we have only one sp using a temp table with primary key and it is not used very ofter. we have greatly reduced the amount of temp tables by using table variables too. I do have the access to the magazine and I've seen the article before but probably missed the point and didn't pay too much attetion to it. thanks for pointing it out and I think that is definitely something I need to check for this issue.
Hi Satya,<br />the article is still related to the 2:1:1, 2:1:3 symptom. <br />maybe I should not worry about the 2:1<img src='/community/emoticons/emotion-12.gif' alt=':x' />xx waits at all as Thomas suggested.<br />
True as long as the temp table creation doesn't dent performance of the long running queries. Keeping up the memory and good placement of data/log files in preferred disks will fetch you optimum performance. Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
