You are currently browsing the category archive for the 'sql 2008' category.
If you intend to BCP spatial data out of SQL Server with the intention of using BULK INSERT/BCP to deploy data to another machine you must ensure both are the same platform. You cannot BCP OUT data from a 32bit instance and BULK INSERT into a 64bit instance with spatial data.
It works fine for standard datatypes; float, int, varchar etc. This may also be an issue for other binary based data but I have only tested this with spatial (geography) data.
I was lucky enough to visit Microsoft during the CTP period and was testing out integrated full text search in 2008. An issue we experienced was that full text can be slow when there is a high number of updates to the index and is caused by blocking on the docidfilter internal table.
It appears to happen if:
- You have AUTO tracking on your full text indexes; although we changed it to manual and still had this issue.
- You experience Full-text queries taking a long time to execute; normally when updates are happening at the same time so you might only see this in production.
- One or more of your queries are complicated or take some time to complete.
You can check to see if your system is slow due to this by:
- SELECT * FROM sys.dm_os_wait_stats statement , it shows very high wait times some of the locks.
- Running Sp_who2; it should consistently show that the full-text gather is blocking full-text queries and, in turn, is being blocked by the queries.
The current work around for this issue is to use a global trace flag microsoft kindly enabled on the RTM build. To use this, type the following Transact-SQL statement:
DBCC TRACEON (7646, -1)
This has a minor side-effect.. which may or may not be important in your organisation. Certainly in ours, it is not. Your full text result may be ever so slightly out of date – e.g. you might return or not return a particular document from the index. However, this issue relates to the dirtyness of an index in terms of 10’s of milliseconds. Important? Unlikely….
I believe there is a knowledge base article on this.
Some great 2008 material here:
http://sqlserver2008jumpstart.microsofttraining.com/content/info.asp?CcpSubsiteID=69&infoid=27
This is an interesting article on an issue with LINQ to SQL and what SQL Server does with procedure caching coming from LINQ.
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/linq-to-sql-queries-involving-strings-ca
In summary, each where condition depending on the size of the value will cause a different plan to be created – e.g if the value passed into a WHERE clause was ‘This Value’ and another was ‘Another Value’ – two entries will be kept in the procedure cache… one a NVARCHAR (10) and another NVARCHAR (13)..
I have no checked SP1 to see if this is resolved. If anyone knows, please let me know.
Missing the report builder, can’t wait for the RTM?
Report Builder 2.0 RC1 is now available on Microsoft Download Center. Use this link for download.
RC1 delivers improvements in the following areas compared to RC0:
- Easy to use wizards for creating table, matrix and chart data regions.
- Support for directly opening and editing reports stored on the report server.
- Support for using server resources such as shared data sources.
- A query designer for Microsoft SQL Server data sources.
- Enhancements to the Report Builder 2.0 ribbon.
So.. enjoy!
For this to work, you need SQL Server 2008.
We use temporary tables and table variables occasionally and sometimes need to fill those temporary objects with data from the results of a stored procedure or dynamically built query (executed with sp_ExecuteSQL). This was a little tricky pre 2008 – as it turns out in SQL Server 2008 this can be done easily.
DECLARE @jobs TABLE
( jobcount int
)
INSERT
INTO @jobs
EXEC
sp_executesql
N’select 1 AS jobcount’
SELECT
*
FROM @jobs
Here, I define a table variable and then insert into this from the execution of a query. I then select from the variable to proove that data was populated. This could easily be a Stored Procedure.
I am sure you can find uses for this , I think this is another good example of the lesser known additions Microsoft has included in 2008.
This may be only relevant to 2008 but if you find Fulltext index creation taking a long time to complete you may be affected by a potential bug in CTP6. If you are experiencing this problem you can try running the following statement prior to the creation of the index. This will disable the checking of the OFFICE filter signature.
sp_fulltext_service
‘verify_signature’, 0
On machines we have here, the signature does not appear to exist for the dll and therefore the crawl is taking a long time to complete.
Well, this is the first of my blog posts about my trip to Seattle. This is not much more than an introductory hello from me in Seattle. I arrived with a stinker of a cold and due to sinus issues during the descent into Seattle I am currently, largely, deaf.
Not a great start to a week of intensive testing with iFTS and SQL Server 2008!.
As a company we rely on Full Text to delivery and with the advent of 2008 , it seems to work differently to how we expect. Therefore, the purpose of this visit is to ensure that 2008 can meet our businesses needs and what changes we should be thinking of implementing to make sure this happens.
In particular , I shall be testing the differences between the different CHANGE TRACKING methods of the full text catalog and the way in which joins to non-full text tables have been given a performance boost. We have made a lot of changes to our indexes (in SQL 2005) to ensure as much as possible is tokenized to negate the need for external joins. However, this does mean that our quieries use multiple ISABOUTS and WEIGHTing to bring back relevant results.
SQL 2008 is potentially a lot different. Future posts will be based on my work this week with Microsoft.
How many times have we had to write multiple INSERT INTO statements? Well, SQL Server 2008 comes to the rescue. Its a small feature but still very useful.
You can now insert multiple rows into a table in a single statement.
INSERT INTO T1 (Col1,Col2,Col3)
VALUES
(‘Row1′, ‘Value’,‘Test’),
(‘Row2′, ‘Value’,‘Test’),
(‘Row3′, ‘Value’,‘Test’)
This inserts 3 rows into the T1 table. Certainly time-saving and makes those sometimes hefty insert scripts smaller.
