Exo-Brain Software Blog

Performance tuning in SQL Server is unendingly complex. At the basic end of the scale are indexes, query plans, disc-based I/O distribution, and the like; more complex issue demand a lot more investigation and care. Because the query optimizer’s job is most heavily centered in disc access - unlike the CLR optimizer, which is best at putting functions inline, and unwinding loops - performance and scalability code can only be realistically tested against data. Lots and lots of data.

Remote joins are a situation SQL Server could handle better. Generally, an entire table is copied from one server to the other, which could potentially flood tempdb, then the join is performed locally. You can use a query hint to force which table is sent and which server the join is delegated to. A better solution will come in a later post, although this is just one situation that requires gigabytes of data to properly test.

By custom, this test data often comes from Cartesian joins. An example is:

Select * From sysObjects, sysComments, sysColumns

My test server doesn’t have enough space to execute this code. If sysComments is removed and the query is executed in a new, empty database, 20,000 rows will be returned ( using SQL Server 2005, Enterprise Edition ). The trouble is, the rows look very much alike, can easily be mistaken for one another, and make poor test data. Let’s try a better approach. Continue Reading »

No Comments »

Anybody who’s ever had to work with a SQL Server object without being told which database it lives in, or worked in a huge schema - like Great Plains or SAP - should find this post interesting. There are several ways to search for a database object by its name or type; we’ll examine a few options, look into the issues involved, and present code to accomplish our search.

The Code

Set NoCount On
Declare @dbName varChar(200), @ins varChar(1000)–Internal use only
Declare
@searchName varChar(200),
@objType varChar(2),
@objTypeList varChar(300),
@owner varChar(200),
@excludeName varChar(100),
@ignoreDatabases varChar(1000),
@sort varChar(1000)
Set @searchName = ‘Log’
/*Set @objTypeList = ‘U, P, V, FN, TR’
Set @objType = ‘P’
Set @owner = ‘dbo’
Set @excludeName = ‘Backup-Log’*/
Set @searchName = IsNull(@searchName, ”)
Set @sort = IsNull(@sort, ‘DatabaseName, SchemaOwner, SqlObjectType’)
Set @ignoreDatabases = IsNull(@ignoreDatabases, ‘model, msdb, tempDB, master, ReportServerTempDB, ReportServer’)
Create Table #sqlSearchResults ( DatabaseName varChar(200), SchemaOwner varChar(100), ObjectName varChar(200), SqlObjectType varChar(2) )
Declare c Cursor Forward_Only For
Select name From master.dbo.sysDatabases
For Read Only
Open c
Fetch Next From c Into @dbName
While @@Fetch_Status = 0 Begin
If PatIndex(’%’ + @dbName + ‘%’, @ignoreDatabases) <= 0 Begin
Set @ins = ‘Insert Into #sqlSearchResults Select ”’ + @dbName + ”’ As DatabaseName, IsNull(U.Name, ”???”), O.Name, xType From ‘ + @dbName + ‘.dbo.sysObjects O Left Join ‘ + @dbName + ‘.dbo.sysUsers U On O.uid = U.uid Where (O.Name Like ”’ + ‘%’ + @searchName + ‘%”)’
If @objType Is Not Null Set @ins = @ins + ‘ And (xType = ‘ + ”” + @objType + ”’)’
If @owner Is Not Null Set @ins = @ins + ‘ And (U.Name = ‘ + ”” + @owner + ”’)’
If @objTypeList Is Not Null Set @ins = @ins + ‘ And (”’ + @objTypeList + ”’ Like ”%” + rTrim(xType) + ”%”)’
If @excludeName Is Not Null Set @ins = @ins + ‘ And (O.Name Not Like ‘ + ”’%’ + @excludeName + ‘%”)’
Exec(@ins)
End
–Print @ins
Fetch Next From c Into @dbName
End
Close c
DeAllocate c
Exec(‘Select * From #sqlSearchResults Order By ‘ + @sort)
Drop Table #sqlSearchResults

Arguments

There are seven variables you can set to control the search. All are optional; passing nothing in will return a list of all objects in all non-system databases. Continue Reading »

5 Comments »