In case anyone is storing their files as blobs in SQL Server and is interested in the best way to export them, check this out:
I have two Great Plains databases, let's call them DB1A and DB1B, that are schematically identical and the data is 99% identical but DB1B has a tiny bit more data. I'm trying to optimize a very complex query that uses views nested in views and is way too long to post all of here. The query was averaging 1 min 43 sec before I started. I noticed that all of the tables involved were heaps (Microsoft's fault, not mine) so I started converting them to clustered one by one, checking the plans and parallelism after each change. The first weird thing is that after I convert 3 or 4 of the tables the query stops using parallelism even though its new plan can result in much longer query times. I got to the point in DB1B where the query only took 1 second, was not parallel, and had low logical reads and zero physical reads. When I updated the tables in DB1A to match those clustered-wise in DB1B, the query plan was different, still using parallelism, and when executed it took 56 seconds and had high logical reads but zero physical reads. ??
The two databases are on the same instance, and I assume SQL Server will decide on its own which database gets to use more of the cache. Since DB1A is production and DB1B is only for my testing I'm guessing DB1A gets a lot more of the cache. If there are zero physical reads, doesn't that mean the results are coming from the cache?
To add to the confusion, I ran UPDATE STATISTICS WITH FULLSCAN on the primary table in the query in both databases, and not only did that not help the query in DB1A, it caused the query in DB1B to slow down to 2+ minutes and do millions of logical reads, just like in DB1A. Is it possible that SQL Server only used the fast query plan in DB1B because the statistics were out of date?? Do you think the cache is playing a bigger role than the stats? Am I missing something?
Also, when I did the UPDATE STATISTICS WITH FULLSCAN it caused my page life expectancy to take a nosedive (at least I think that was the cause).
I have a real-time reporting server with a database which is basically a copy of my production database, but it's populated using transactional replication and replication only copies the primary keys over, i.e. none of the secondary indexes are included. So what I do is every time I need to recreate replication--which is every deployment with schema changes--I script out DROPs and CREATEs for all the reporting indexes I've created and then run the script after replication has been recreated. The script I use is a modified conglomeration of scripts I've found on the Internet, but you may find it useful. Note that it excludes primary keys and some other things.
PRINT '------------------------- DROP INDEXES ----------------------------------------------------'
DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @indexname SYSNAME
DECLARE @sql NVARCHAR(4000)
DECLARE dropindexes CURSOR FOR
SELECT indexes.name, objects.name, schemas.name
FROM sys.indexes
JOIN sys.objects ON indexes.OBJECT_ID = objects.OBJECT_ID
JOIN sys.schemas ON objects.schema_id = schemas.schema_id
WHERE indexes.index_id > 0
AND indexes.index_id < 255
AND objects.is_ms_shipped = 0
AND NOT EXISTS (SELECT 1 FROM sys.objects WHERE objects.name = indexes.name)
ORDER BY indexes.name
--SELECT * FROM sys.stats
OPEN dropindexes
FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
WHILE @@fetch_status = 0
BEGIN
SET @sql = N'IF EXISTS (SELECT 1 FROM sys.indexes i WHERE i.name = ''' + @indexname + ''') DROP INDEX '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@indexname)
PRINT @sql
FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
END
CLOSE dropindexes
DEALLOCATE dropindexes
PRINT CHAR(10) + CHAR(13) + '------------------------- CREATE INDEXES ----------------------------------------------------'
declare
@object_id int,
@index_id tinyint,
@schema_name sysname,
@table_name sysname,
@index_name sysname,
@type tinyint,
@uniqueness bit,
@indexed_column sysname,
@included_column sysname,
@indexed_columns varchar(max),
@included_columns varchar(max),
@has_included_cols bit,
@is_descending_key bit,
@stmt varchar(max),
@crlf char(2)
set @crlf = char(13) + char(10)
declare indexes cursor
for
select
schema_name = s.name,
table_name = t.name,
index_id = i.index_id,
index_name = i.name,
type = i.type,
uniqueness = i.is_unique
from
sys.schemas s
join sys.tables t on s.schema_id = t.schema_id
join sys.indexes i on t.object_id = i.object_id
where
i.type > 0 -- none -heap
AND I.index_id > 0 -- ignore PKs
AND I.index_id < 255
AND i.type = 2
AND INDEXPROPERTY (I.object_id,I.NAME,'ISCLUSTERED') =0
AND left(I.object_id,3) not in ('sys', 'dt_')
AND left(I.name,3) not in ('PK_', 'UQ_','nci','ucm','ci_','uk_')
order
by i.name
open indexes
fetch
indexes
into
@schema_name,
@table_name ,
@index_id ,
@index_name ,
@type ,
@uniqueness
while @@fetch_status<>(-1)
begin
select @object_id = object_id(@schema_name + '.' + @table_name)
set @indexed_columns = '('
declare indexed_columns cursor
for
select
c.name,
ic.is_descending_key
from
sys.index_columns ic
join sys.columns c on ic.column_id = c.column_id
and ic.object_id = c.object_id
where
ic.object_id = @object_id
and ic.index_id = @index_id
and ic.is_included_column = 0
order by
ic.index_column_id
open indexed_columns
fetch indexed_columns
into @indexed_column, @is_descending_key
while @@fetch_status<>(-1)
begin
set @indexed_columns = @indexed_columns + @indexed_column +
case @is_descending_key when 1 then ' DESC ' else '' end + ', '
fetch indexed_columns
into @indexed_column, @is_descending_key
end
close indexed_columns
deallocate indexed_columns
set @indexed_columns = left(@indexed_columns, len(@indexed_columns)-1) + ')'
if exists
(select object_id
from sys.index_columns
where object_id = @object_id
and index_id = @index_id
and is_included_column = 1 )
begin
set @included_columns = 'INCLUDE ('
declare included_columns cursor
for
select
c.name,
ic.is_descending_key
from
sys.index_columns ic
join sys.columns c on ic.column_id = c.column_id
and ic.object_id = c.object_id
where
ic.object_id = @object_id
and ic.index_id = @index_id
and ic.is_included_column = 1
order by
ic.index_column_id
open included_columns
fetch included_columns
into @included_column, @is_descending_key
while @@fetch_status<>(-1)
begin
set @included_columns = @included_columns + @included_column +
case @is_descending_key when 1 then ' DESC ' else '' end + ', '
fetch included_columns
into @included_column, @is_descending_key
end
close included_columns
deallocate included_columns
set @included_columns = left(@included_columns, len(@included_columns)-1) + ') '
end
set @stmt =
'CREATE ' + case @uniqueness when 1 then 'UNIQUE ' else ' ' end + case @type when 1 then 'CLUSTERED ' else '' end +
'INDEX ' + @index_name + ' ON ' + @schema_name + '.' + @table_name + @indexed_columns + ' ' + isnull(@included_columns,'') + @crlf
set @included_columns = ''
print @stmt
fetch
indexes
into
@schema_name,
@table_name ,
@index_id ,
@index_name ,
@type ,
@uniqueness
end
close indexes
deallocate indexes
If you're like me you've been using SSRS 2008 for a long time with TFS and have constantly had to delete the bin folder in order for builds to work. This has irritated me to no end. Well today is my lucky day! There is a hotfix that actually fixes the bug! Go here: http://support.microsoft.com/kb/2438347
I recently discovered that creating indexes in one of my production databases was causing deadlocks. My problem was that I wasn't using ONLINE=ON when I was creating the index. Check out the BOL for more information.
If you're like me you had at one time connected one of your Reporting Services instances to a report server database that was already in use by another instance. This allows the instance to show up in the Scale-out Deployment section of the Reporting Services Configuration Manager. My problem was that the server that got joined to the original server was no longer available as it had been repurposed, and when I clicked Remove Server to remove it from my scale-out it would fail because it couldn't contact the server. After searching for a solution for quite some time I decided to look around in the report server database tables, and voila! All I had to do was remove the old server from the Keys table. I can't guarantee there won't be any side effects to this method, but it worked like a charm for me.
For those of you who, like me, transitioned from Subversion and TortoiseSVN to TFS, you might be confused as to how to view the history of a folder recursively. My problem was that I was wanting to see the recursive history in the Solution Explorer rather than the Source Control Explorer.
If there's already a way to get a List<int> of consecutive integers without a loop in C#, I don't know what it is, so I created a method for it.
public static List<int> GetIntegerListFromRangeUsingLoop(int start, int end) {
if (end < start) {
throw new ArgumentException("Faulty parameter(s) passed: lower bound cannot be less than upper bound.");
}
List<int> returnList = new List<int>(end - start + 1);
for(int i = start; i <= end; i++) {
returnList.Add(i);
}
return returnList;
}
UPDATE:
I was pointed to Enumerable.Range(), so I updated my code.
public static List<int> GetIntegerListFromRangeUsingEnumerableRange(int start, int end) {
IEnumerable<int> list = Enumerable.Range(start, end - start + 1);
return list.ToList();
}
Then I used the StopWatch class in my unit tests to compare the two methods.
Results:
GetIntegerListFromRangeUsingEnumerableRange averaged 6.5 milliseconds.
GetIntegerListFromRangeUsingLoop averaged 0.43 milliseconds.
Wow. Apparently Enumerable.Range() is a lot slower than using a loop.
I really like Windows 7 so far, amazingly, but I was about ready to hate it because my computer kept freezing when I would try to copy large files to the network. After about a week I realized that even though Windows Update is telling me it has no updates for me, if I force it to check for new updates it will find them (until I install the ones it finds). After installing the latest driver for my Atheros NIC, the problem seems to have disappeared (I could recreate it until now, so I'm pretty sure that fixed it).
How is it that you still can't save workspaces in SQL Server Management Studio as of version 2008? It seems like such a simple thing to implement. Quite often I create a bunch of different queries during a short period that are specific to a task I'm working on and that won't be needed once the task is completed, and rather than having to save each one it would be nice if I could just save a workspace file that knows which queries were open.
For anybody that bought the
SQL Server 2008 Self-Paced Training book for 70-433, if you are running a full version of SQL Server 2008 rather than the express edition, you will have problems attaching the AdventureWorks2008 database (file activation error). The trick is to use a SQL command, like so:
USE [master]
GO
CREATE DATABASE [AdventureWorks2008] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\AdventureWorks2008_Data.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\AdventureWorks2008_Log.ldf' ),
FILEGROUP Documents CONTAINS FILESTREAM (NAME = Documents, FILENAME=N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\Documents')
FOR ATTACH
GO
I spent the past day trying to figure out why I couldn't log in as myself, Sharepoint would always just say I was logged in as System Account. After a ton of research and changing things I ran into this:
To fix this, you must run the following commands: stsadm -o updatefarmcredentials -identitytype NetworkService
followed by: iisreset
http://social.technet.microsoft.com/Forums/en-US/sharepointadmin/thread/8d8d9b30-0995-47ba-96e8-7b52872664f3
That solved my problem. I'm using NTLM authentication and I set up a new Active Directory user called sharepointuser to be the identity for the app pool (changing the app pool from myself to that user probably helped, but that isn't what fixed it ultimately). I'm pretty sure if I had set up sharepointuser before I installed MOSS 2007 on the Windows 2003 server and specified that user during the installation, I wouldn't have had the auth problems to begin with.
Here's a Powershell script I whipped up to copy all bak files from C:\TEMP to Y:\DEST. It checks to make sure the file is present at the destination before it deletes it from the source. I placed it in a ps1 (the extension for Powershell files) file and scheduled a task to execute a batch file that executes the ps1 file. The reason I had to check for null is that Powershell is kinda dumb and if the source folder is empty it picks it up as a child and performs the Copy-item.
$oldPath = "C:\TEMP"
$newPath = "Y:\DEST"
$GciFiles = get-childitem $oldPath
foreach ($file in $GciFiles){
Write-host $file.Name
if(!($file.Name -eq $null) -and $file.Name.EndsWith(".bak")){
Write-host "copying item "+($newPath+'\'+$file)
Copy-item $oldPath\$file $newPath
if (Test-path ($newPath+'\'+$file)) {
Write-host "removing item "+($oldPath+'\'+$file)
Remove-item ($oldPath+'\'+$file) -recurse
}
}
}
I spent hours trying to figure out why my ASP.Net page, which uses a master page with a RadGrid surrounded by a RadAjaxPanel with an AjaxLoadingPanel, would not show the loading message. I would click on the link to the page in the site menu, then IE would sit and spin for a while until it loaded the entire page, rather than loading the page right away but showing the loading spinner until the data was returned. What I had to do was add this javascript (w/jQuery) to the user control that the grid was in:
<script type="text/javascript">
$(document).ready(function() {
setTimeout(loadGrid, 500);
});
function loadGrid() {
window['<%=RadAjaxPanel1.ClientID %>'].AjaxRequest('');
}
</script>
I'm not sure why I had to make it sleep for half a second, but it wouldn't work until I added that.
I set up a server-side event (OnAjaxRequest="RadAjaxPanel1_AjaxRequest") that fires when AjaxRequest is called in the javascript:
protected void RadAjaxPanel1_AjaxRequest(object sender, AjaxRequestEventArgs e) {
Presenter.InitializeView();
BindGrid();
}