10 Secretos de SQL Server 2005

TableDiff.exe







Table Difference tool allows you to discover and reconcile differences between a source and destination table or a view. Tablediff Utility can report differences on schema and data. The most popular feature of tablediff is the fact that it can generate a script that you can run on the destination that will reconcile differences between the tables. TableDiff.exe takes 2 sets of input;










Connectivity – Provide source and destination objects and connectivity information.


Compare Options – Select one of the compare options













Compare schemas: Regular or Strict


Compare using Rowcounts, Hashes or Column comparisons


Generate difference scripts with I/U/D statements to synchronize destination to the source.


TableDiff was intended for replication but can easily apply to any scenario where you need to compare data and schema.


You can find more information about command line utilities and the Tablediff Utility in Books Online for SQL Server 2005.

2

Triggers for Logon Events (New in Service Pack 2)













With SP2, triggers can now fire on Logon events as well as DML or DDL events.


Logon triggers can help complement auditing and compliance. For example, logon events can be used for enforcing rules on connections (for example limiting connection through a specific username or limiting connections through a username to a specific time periods) or simply for tracking and recording general connection activity. Just like in any trigger, ROLLBACK cancels the operation that is in execution. In the case of logon event that means canceling the connection establishment. Logon events do not fire when the server is started in the minimal configuration mode or when a connection is established through dedicated admin connection (DAC).


The following code snippet provides an example of a logon trigger that records the information about the client connection.

CREATE TRIGGER connection_limit_trigger
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO logon_info_tbl SELECT EVENTDATA()
END;

You can find more information about this feature in updated Books Online for SQL Server Services Pack 2 un the heading “Logon Triggers”.

3

Boosting performance with persisted-computed-columns (pcc).













Btree Indexes provide great compromise for tuning queries vs redundant storage of data and added cost of modifying data (insert/update/delete). A less known capability for tuning in SQL Server 2005 is persisted computed columns (PCC). Computed columns can help you shift the runtime computation cost to data modification phase. The computed column is stored with the rest of the row and is transparently utilized when the expression on the computed columns and the query matches. You can also build indexes on the PCC’s to speed up filtrations and range scans on the expression.


The following sample can demonstrate the benefits of a persisted computed column applied to a complex expression. The same TSQL query run against the following table schema with and without the DayType column will demonstrate the effect of the transparent expression matching with persisted computed columns. The output from the sys.dm_exec_query_stats DMV also shows the difference in the IO and CPU characteristics of the query.


Query

SELECT [Ticker] ,[Date] , [DayHigh] ,[DayLow] ,[DayOpen] ,[Volume] ,[DayClose] ,[DayAdjustedClose],
CASE
WHEN volume > 200000000 and dayhigh-daylow /daylow > .05 THEN ‘heavy volatility’
WHEN volume > 100000000 and dayhigh-daylow /daylow > .03 THEN ‘volatile’
WHEN volume > 50000000 and dayhigh-daylow /daylow > .01 THEN ‘fair’
ELSE ‘light’
END as [DayType]
FROM dbo.MarketData
WHERE
CASE
WHEN volume > 200000000 and dayhigh-daylow /daylow > .05 THEN ‘heavy volatility’
WHEN volume > 100000000 and dayhigh-daylow /daylow > .03 THEN ‘volatile’
WHEN volume > 50000000 and dayhigh-daylow /daylow > .01 THEN ‘fair’
ELSE ‘light’
END = ‘heavy volatility’

Table Schema

CREATE TABLE [dbo].[MarketData](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Ticker] [nvarchar](5) NOT NULL,
[Date] [datetime] NOT NULL,
[DayHigh] [decimal](38, 6) NOT NULL,
[DayLow] [decimal](38, 6) NOT NULL,
[DayOpen] [decimal](38, 6) NOT NULL,
[Volume] [bigint] NOT NULL,
[DayClose] [decimal](38, 6) NOT NULL,
[DayAdjustedClose] [decimal](38, 6) NOT NULL,
— PERSISTED COMPUTED COLUMN —
[DayType] AS (
CASE
WHEN volume > 200000000 and dayhigh-daylow /daylow > .05 THEN ‘heavy volatility’
WHEN volume > 100000000 and dayhigh-daylow /daylow > .03 THEN ‘volatile’
WHEN volume > 50000000 and dayhigh-daylow /daylow > .01 THEN ‘fair’
ELSE ‘light’
END) PERSISTED NOT NULL
) ON [PRIMARY]

Output From The Sys.Dm_Exec_Query_Stats Dynamic Management View (DMV)



In the above picture, the output from sys.dm_exec_query_stats dynamic management view shows the difference in CPU and IO statistics between the same query hitting MarketData_Computed and MarketData tables. Line 1 represents the query run against the table with the persisted computed column. Line 2 is the table without the persisted computed column. With the complex expression pre-calculated in the DayType column, total worker time and overall elapsed time is lower compared to the table without the DayType persisted computed column.


•Another way to verify that the persisted computed column is utilized, is to use the execution plan and look at the scan or the seek operator for the table with the computed column and check the output list, which should contain the column. In the example below you can see the DayType, the name for the PCC, in the output list under #9.


4

DEFAULT_SCHEMA setting in sys.database_principles













SQL Server provides great flexibility with name resolution. However name resolution comes at a cost and can get noticeably expensive in adhoc workloads that do not fully qualify object references. SQL Server 2005 allows a new setting of DEFEAULT_SCHEMA for each database principle (also known as “user”) which can eliminate this overhead without changing your TSQL code. Here is an example;


In SQL Server 2005, the following query when executed by user1 that has a DEFAULT_SCHEMA of ‘dbo’ will directly resolve to dbo.tab1, instead of the extra search for user1.tab1.

SELECT * FROM tab1

Whereas the same query will search for ‘user1.tab1’ in SQL Server 2000 and if that does not exist it will resolve to ‘dbo.tab1’.


This setting can be especially useful for databases upgraded from SQL Server 2000 to SQL Server 2005. To preserve the original behavior, databases upgraded from SQL Server 2000 will get the username as the DEFAULT_SCHEMA for each database principle. That means, in a database upgraded from a previous version to SQL Server 2005, ‘user1’ will get a DEFAULT_SCHEMA values of ‘user1’. To take advantage of the performance benefits, administrators can set the DEFAULT_SCHEMA through ALTER USER command and change it to the schema that most of the of the objects reside. Be aware this may break queries that may be utilizing objects in other schemas than the one set in the DEFAULT_SCHEMA setting and has not qualified the object names.


DEFULT_SCHEMA is documented in Book Online under the “CREATE USER (Transact-SQL)” heading.

5

Forced Parameterization
















Parameterization allows SQL Server to take advantage of query plan reuse and avoid compilation and optimization overheads on subsequent executions of similar queries. However there are many applications out there that, for one reason or another, still suffer from ad-hoc query compilation overhead. For those cases with high number of query compilation and where lowering CPU utilization and response time is critical for your workload, force parameterization can help.


Force parameterization forces most queries to be parameterized and cached for reuse in subsequent submissions. Forced parameterization will remove the literal values and replaces them with parameters. This minimizes the compilation overhead for queries that are the same except the literal values in the query text. Forced parameterization is typically enabled at the database level. However it is also possible to hint FORCED PARAMETERIZATION on individual queries.


In a number of cases, we have witnessed improvements in performance up to 30% due to forced parameterization. However forced parameterization can cause inappropriate plan sharing in cases where a single execution plan does not make sense. For those cases, you can utilize features like plan guides or query hints.


You can find more information on Forced Parameterization in Books Online.

6

Vardecimal Storage Format






















In Service Pack 2, SQL Server 2005 adds a new storage format for numeric and decimal datatypes called vardecimal. Vardecimal is a variable-length representation for decimal types that can save unused bytes in every instance of the row. The biggest amount of savings come from cases where the decimal definition is large (like decimal(38,6)) but the values stored are small (like a value of 0.0) or there is a large number of repeated values or data is sparsely populated.


SQL Server 2005 also includes a stored procedure that can estimate the savings before you enable the new storage format.

master.dbo.sp_estimate_rowsize_reduction_for_vardecimal ‘tablename’

To enable vardecimal storage format, you need to first allow vardecimal storage on the database;

exec sys.sp_db_vardecimal_storage_format N’databasename’, N’ON’

Once the database option is enabled, you can then turn on vardecimal storage at a table level using the following procedure;

exec sp_tableoption ‘tablename’, ‘vardecimal storage format’, 1

Vardecimal storage format presents an overhead due to the complexity inherent in variable length data processing. However in IO bound workloads, savings on IO bandwidth due to efficient storage can far exceed this processing overhead.


If you would like more information on this topic, updated SQL Server 2005 Books Online for Service Pack 2 contains extensive information on the new vardecimal format.

7

Indexing made easier with SQL Server 2005
















The new Dynamic Management Views have improved monitoring and trouble shooting greatly. A few of the dynamic management views (DMVs) deserve special attention.


Through sys.dm_index_usage_stats you can find out how much maintenance and traversal you have for each index. Indexes with high maintenance numbers and low traversal numbers can be considered as good candidates for dropping.


Through sys.dm_db_missing_index_* collection of DMVs, you can get recommendations on what new indexes could benefit the queries running on your server. The recommendations come with a estimate on how much improvement you can expect from the new index.


If you’d like to automate creation and dropping of indexes, SQL Server Query Optimization Team has blogged about how to automate index recommendations into actions: http://blogs.msdn.com/queryoptteam/archive/2006/06/01/613516.aspx

8

Figuring out the most popular queries in seconds













Another great DMV that can help save you a lot of work is sys.dm_exec_query_stats. In previous version of SQL Server to find out the highest impact queries on CPU or IO in system, you had to walk through a long set of analyses steps including getting aggregated information out of the data you collected from profiler.


With sys.dm_exec_query_stats, you can figure out many combinations of query analyses by a single query. Here are some of the examples;
















Find queries suffering most from blocking –
(total_elapsed_time – total_worker_time)


Find queries with most CPU cycles –
(total_worker_time)


Find queries with most IO cycles –
(total_physical_reads + total_logical_reads + total_logical_writes)


Find most frequently executed queries –
(execution_count)


You can find more information on how to use dynamic management views for performance troubleshooting in the “SQL Server 2005 Waits and Queues” whitepaper located at: http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx

9

Scalable Shared Databases










Scalable Shared Databases provide an alternative scale out mechanism for Read-Only environments. Through Scalable Shared Databases one can mount the same physical drives on commodity machines and allow multiple instances of SQL Server 2005 to work off of the same set of data files. The setup does not require duplicate storage for every instance of SQL Server and allows additional processing power through multiple SQL Server instances that have their own local resources like cpu, memory, tempdb and potentially other local databases. However this type of setup does limit the IO bandwidth since all instances point to the physical set of files.



Book Online for SQL server 2005 contains details on Scalable Shared Databases.

10

Soft-NUMA













Highly concurrent workloads hit a contention point around global state they maintain at some point. That point in many cases happen to be ‘8’. One way around this contention has been to eliminate the global state and create hierarchies. NUMA architectures allow us to eliminate the contention around global resources by moving main resources closer to each other and forming nodes. SQL Server 2005 recognizes the NUMA architecture and self manages allocation of resources to adhere and take advantage of the hardware NUMA setup at the time of startup. By aligning with the HW setup SQL Server partitions its internal management to improve throughput.


Some workloads benefit greatly from the partitioning concept, especially mixed workloads that have to run varying characteristics of data access concurrently (example: OLTP and Reporting). Soft-NUMA allows partitioning configuration to be extended into the software level and defined either on top of a NUMA enabled environment to further divide the hardware partitions into smaller chunks or on a machine that does not utilize NUMA concepts to enable partitioning for the configuration. By configuring partitions through Soft-NUMA, the administrator can control the allocation of schedulers and memory managers for each node and can configure specific TCP/IP ports for the nodes. Then, clients can be configured to connect using the specific ports to access specific partitions.



Soft-NUMA topic is extensively covered in Book Online. You can also read more about the details of Soft-NUMA at Slava Oks’s Weblog at http://blogs.msdn.com/slavao/


De: http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/gems-top-10.mspx

Cosas Interesantes 22/02/2007

Hoy en cosas interesantes: SQL Server 2005 Best Practices Analyzer, Nuevo sitio de Microsoft sobre BI, Nueva beta de SCCM 2007, Pantallazo del acceso remoto en Home Server, Cuidadito con el SP2 de SQL Server, Mitos de SQL Server, BrainDump Personal.


SQL Server 2005 Best Practices Analyzer.


Me encantan estas herramientas de buenas practicas, te permiten validar rapidamente si cumples con los paremetros diseñados por el equipo de desarrollo del producto.


En esta ocasión le toca a SQL Server 2005, estoy seguro de que esta herramienta os dara buenos consejos.


Es una versión CTP.


http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en


Nuevo sitio de Microsoft sobre BI.


Bueno, todo lo relacionado con Business Inteligence esta siempre interesante, Microsoft ha abierto un nuevo sitio para unificar mucha de la información disponible.


Os recomiendo la visita.


http://www.microsoft.com/bi/


Nueva beta de SCCM 2007.


Soy feliz, el equipo de MOM me ha pasado nueva build del producto y los de SMS sacan tambien beta 😀 hoy no duermo.


Podeis acceder a la beta 2 de SCCM 2007 como siempre en http://connect.microsoft.com/


Recordar que teneis que montar tambien WSUS 3.0 beta.


Pantallazo del acceso remoto en Home Server.


Una de las cosas que mas me gusta de Home Server es la posibilidad de acceder desde intenet a cualquier equipo dentro de casa.


Os dejo un pantallazo.



Cuidadito con el SP2 de SQL Server.


Pues eso, mucho cuidadito que no se puede desinstalar, si teneis problemas tendreis que reinstalar desde 0.


http://blogs.msdn.com/chadboyd/archive/2007/02/21/sp2-rollback-requires-complete-reinstallation.aspx


Mitos de SQL Server.


Buen articulo que discute algunos de los mitos sobre SQL Server.


http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx


BrainDump Personal.


2 monitores de 24 pulgadas no son suficientes, tener servidores en casa hace demasiado ruido, Star Treck no terminara nunca, esta noche tengo que terminar un articulo sobre ISA Server 2006 y ya llevo escritas 7000 palabras, ISA Server 2006 es espectacular y quiero probar alguna beta de las que han salido hoy.


El rifle springfield del Call of duty de la XBOX 360 es espectacular 😀 , el hospital de la serie anatomia de Grey esta en Seattle si pasas por Microsoft en Redmon y tienes tiempo de pasarte por ese hospital las probabilidades estadisticas de ligar con Grey son altas.


Dormir es necesario pero no es imprescindible.


Mañana puede que mas……


 


 

Lo se, lo se estan todos comprados.

Parece que el mercado del Olap tiene claro vencedor. 



http://blogs.technet.com/emmab/archive/2007/02/20/still-1-on-olap-report.aspx


http://www.olapreport.com/


Y por ultimo un mes mas, apache cae e IIS sube, y como digo siempre el IIS 7 killer entre los killers esta a la vuelta de la esquina.


Graph of market share for top servers across all domains, August 1995 - February 2007


http://news.netcraft.com/archives/web_server_survey.html


Correo, ya veremos con Exchange 2007 como se pone el tema, aunque es cierto, que no creo que aqui gane Microsoft, ya que en Internet la mayoria de ISPs etc no montan exchange por el tema de las licencias, seria util una estadistica solo entre empresas finales sin ISPs ni Hostings.


Pero desde luego como servidor de correo empresarial, no creo que nadie pueda decir que Exchange 2007 tiene competencia.




























Server Type Number of Servers Percent
Sendmail 288,277 32.09%
Microsoft 188,670 21.00%
Exim 166,392 18.52%
Postfix 119,833 13.34%
IMail 36,909 4.11%

Cosas Interesantes 20/02/2007

Hoy en cosas Interesantes: CTP de Febrero de Longhorn, Virtual PC 2007, SQL RS SP2 y Sharepoint, Management Pack de MOM para KMS, Xbox 360 negra.


CTP de Febrero de Longhorn.


Los rumores hablan de Beta 3 para Marzo, de momento tenemos esta nueva CTP de la que de momento no tenemos información, en mi caso ya se esta bajando, espero poder contar algo mas en unos dias.


Virtual PC 2007.


Bueno, el mismo dia que nos dan la alegria de sacar el SP2 de SQL Server nos anuncian tambien la versión final del gratuito Virtual PC 2007, recordar que tiene soporte para vista, host de 64 bits y virtualización asistida por hadware.


Gran producto si señor.


http://www.microsoft.com/downloads/details.aspx?FamilyId=04D26402-3199-48A3-AFA2-2DC0B40A73B6&displaylang=en#filelist


SQL RS SP2 y Sharepoint.


El SP2 de SQL Server trae varias mejoras y novedades en su modulo de Reporting Services, algunas de ellas orientadas a la integración de informes dentro de Sharepoint.


Varios ejemplos son las webparts para ver informes o listarlos y las librerias de informes.



Mas info y guia de instalación


Management Pack de MOM para KMS.


Ahora que ya podemos tener nuestro KMS montado sobre un server 2003 en condiciones Microsoft nos ofrece tambien este management pack de MOM para poder tener monitorizada nuestra infraestructura de gestión de licencias de Vista.


http://go.microsoft.com/fwlink/?LinkId=83014


Xbox 360 negra.


blac360mock.jpg


Dicen que tendra disco de 120Gb y que saldra en Mayo, pero tiene pinta de FUD.


Mañana mas……

Seguro que ya lo sabeis, pero el SP 2 de SQL Server ya esta aqui!!!!!!!1

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx


Son muchas las mejoras en diferentes areas tales como Analysis Services, Reporting Services, Rendimiento, planes de mantenimiento, Replicación, integración con otros sistemas de BD, herramientas y administración


Todo lo nuevo del SP2.


http://download.microsoft.com/download/2/b/5/2b5e5d37-9b17-423d-bc8f-b11ecd4195b4/WhatsNewSQL2005SP2.htm


Espero que la instalación en cluster sea mejor 😀