There are a few ways to insert query results into another table. The most common way is to use the standard insert statement. This would insert query results into a table that already contains data. [cc lang=”sql”] INSERT INTO dbo.Users ( Username ,FirstName ,LastName ,IsSuperUser ,AffiliateId ,Email ,DisplayName ,UpdatePassword ) SELECT Username ,FirstName ,LastName ,IsSuperUser ,AffiliateId ,Email ,DisplayName = DisplayName + ‘ SuperUser’ ,UpdatePassword FROM dbo.NewUsers nu WHERE nu.IsSuperUser = 1 [/cc] This way is most efficient if you are inserting records into a new table. It will automatically create the table for you and copy the datatypes from the existing table and all the rows: [cc lang=”sql”] SELECT * INTO dbo.EventLogCopy FROM dbo.EventLog [/cc]
Continue reading ...
The following list of performance counters have been modified to include the most pertinent in identifying the bottleneck of your system. This list is for reference purposes. If needing to troubleshoot slowness read troubleshooting sql server slowness. Memory Object: – Memory Counter: – Available Mbytes Preferred Value: – > 2000MB Description: With 64 bit OS’s the available bytes recommendation has increased. Object: – Memory Counter: – Pages/Sec Preferred Value: – < 50 Description: - Pages/sec is the rate at which pages are read from or written to disk to resolve hard page faults. Indicative of possible page file usage, and definitely a memory bottleneck somewhere on the system. Object: - Paging File Counter: - %Usage Preferred Value: - < 5% Description: - Current amount of Page file being used. This is a key number when weighing the amount of memory allocated to the OS. If this number is high, then the OS is choked for RAM. Either increase the RAM on the box or deallocate from SQL. If this is between 10% and 25% you should reboot. Object: - Paging File Counter: - %Usage Peak Preferred Value: - < 15% Description: - Show the peak the page file got to since the last reboot. Usually this is not too far off from the page file usage %. Unfortunately when page file issues happen, they do not correct themselves without a reboot. Object: - SQL Server:Buffer Manager Counter: - Page Life Expectancy Preferred Value: - > 300 Description: – The single […]
Continue reading ...
SELECT SUM(rowcnt)
FROM sysindexes
WHERE indid < 2
Continue reading ...
SELECT
spid
,status
,loginame = SUBSTRING(loginame, 1, 12)
,hostname = SUBSTRING(hostname, 1, 12)
,blk = CONVERT(char(3), blocked)
,open_tran
,dbname = SUBSTRING(DB_NAME(dbid),1,10)
,cmd
,waittype
,waittime
,last_batch
FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0
Continue reading ...
DBCC TRACEON (1204, -1)
Continue reading ...
